题解 | 2021年11月每天新用户的次日留存率

-- 逻辑梳理：留存率的题目一定要准备两张表，注册表是登陆表，如果非新用户那么就是两行登陆表
with tmp_1 as(
    select
    uid
    ,min(in_time) first_login
    from tb_user_log
    group by uid
),-- 这里有个非常大的坑点，不能在tmp1里直接加where筛选11月，因为如果这里加了11月的筛选，只能求出来用户在11月的第一次登录日期，这不一定是用户的注册日期
tmp_2 as(
    select
    uid
    ,date(in_time) in_time
    from tb_user_log
    union  -- 将两个表的in和out通过union相连，来解决如果一个用户的登录跨天，那么会被视为两天都活跃
    select
    uid
    ,date(out_time) in_time
    from tb_user_log
)


select  -- 这里的核心，就是通过注册表和活跃表连接，加上限制条件去判断用户是否次日活跃，如果活跃那么b.in_time一定是有值的，这是计算留存率的核心逻辑
date(a.first_login) dt
,round(count(b.in_time) / count(a.first_login),2) uv_left_rate
from tmp_1 a
left join tmp_2 b
on a.uid = b.uid
and date(a.first_login) = date_sub(b.in_time,interval 1 day)
where left(a.first_login,7) = '2021-11'
group by date(a.first_login)