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

#每个用户完整的活跃时间表，这一步是将跨天的活跃也取到
with comp as(
select uid , date(in_time) as dt
from tb_user_log
UNION
select uid , date(out_time) as dt
from tb_user_log
order by uid)
,
#新用户首次登录表
new as(
select uid,min(date(in_time)) dt
from tb_user_log
group by uid) 
,
#每天新用户的次日留存数
1_left as(
    select dt,
        count(1_left_user) 1_left
    from(
        select n.dt,
            if(datediff(c.dt,n.dt)=1,n.uid,null) 1_left_user #显示次日留存的用户的用户名
            #if(datediff(b.dt,a.dt)=1,1,0) 1_left_user  #仅将每日的次日留存用户记为1，想用sum计算那就用这个
        from new n join comp c
        where n.uid=c.uid and n.dt<c.dt
    ) a
    group by dt
)
#连接每天新用户的数量及每天新用户的次日留存数，计算每天新用户的次日留存率
select dt,
    if(round(1_left/t_left,2) is null,0.00,round(1_left/t_left,2)) uv_left_rate from (
select dt,count(uid) t_left
from new
group by dt) a #每天新用户的数量
left join 1_left using(dt)
where month(dt) =11 #以上把数据库里每天的新用户次日留存率都算了，最后筛选一下只要11月的。