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

with t2 as (
    select uid,date(in_time) as in_time from tb_user_log
    union 
    select uid,date(out_time) as in_time from tb_user_log
)
,
t1 as (
    select uid,
    min(date(in_time)) as min_log
    from tb_user_log
    group by uid
)

select min_log as dt,
round(next_log/new_log,2) as uv_left_rate
from(
select min_log,
sum(case when diff=0 then 1 else 0 end) as new_log,
sum(case when diff=1 then 1 else 0 end) as next_log
from(
select t.uid,
date(in_time) as in_time,
min_log,
datediff(in_time,min_log) as diff
from t2 t
left join t1
on t.uid=t1.uid
) a
where min_log between '2021-11-01' and '2021-11-30'
group by min_log
)b