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

with t1 as
(select 
    uid,
    date(in_time) as dt,
    date(min(in_time) over(partition by uid)) as mindt
from tb_user_log
union 
select 
    uid,
    date(out_time) as dt,
    date(min(in_time) over(partition by uid)) as mindt
from tb_user_log
)
select 
    mindt,
    round(sum(dif)/count(distinct uid),2) as uv_left_rate
from (
select 
    uid,
    dt,
    mindt,
    if(mindt=date_sub(dt, interval 1 day), 1, 0) as dif
from t1  
)t2
where date_format(mindt, '%Y%m') = 202111
group by mindt