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

select 
    a.dt,
    round(count(b.uid)/count(a.uid),2) as uv_left_rate
from
-- 每位用户最开始进入的时间
    (select 
        uid,
        min(date(in_time)) as dt
    from tb_user_log
    group by uid) a
left join
-- 所有用户活跃的时间
    (select uid, date(in_time) as dt
    from tb_user_log
    union
    select uid, date(out_time) as dt
    from tb_user_log) b
on a.uid=b.uid and datediff(b.dt,a.dt)=1
where a.dt>='2021-11-01'
group by dt
order by dt