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

with a as(
    #首次登陆日期
select 
    uid,
    date(min(in_time)) as date
from tb_user_log
group by uid
),
b as(
    #全部活跃日期
select uid,
       date(in_time) as date
from tb_user_log
union
select uid,
       date(out_time) as date
from tb_user_log
order by uid 
),
c as(
select a.uid as uid,
    a.date as first_date,
    b.date as date
from a
left join b on a.uid=b.uid
    and a.date=date_sub(b.date,interval 1 day)
)
select 
    first_date as dt,
    round(count(date)/count(first_date),2) as uv_left_rate
from c
where month(first_date)=11
group by first_date
order by dt
;