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

select
    t1.min_date as dt,
    round(sum(if(t2.dt is null,0,1))/count(t1.uid),2) as uv_left_rate
from
    -- 第一步：找出新用户表
    (
        select
            uid,
            date (min(in_time)) as min_date
        from tb_user_log
        group by uid
    ) t1
    -- 第二步，新用户表和用户活跃表进行连接，限制左表日期小于右表日期
    -- 计算活跃表时，记得把进入时间和退出时间所在日期都计算上
    left join (
        select
            uid,
            date (in_time) dt
        from tb_user_log
        union
        select
            uid,
            date (out_time) dt
        from tb_user_log
    ) t2 on t1.uid = t2.uid
    and datediff (t2.dt, t1.min_date) = 1
where t1.min_date between '2021-11-01' and '2021-11-30'
group by t1.min_date
order by t1.min_date