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

with t1 as(
    select
uid,
min(date(in_time)) new_dt
from
tb_user_log
group by uid
),t2 as(
   select
uid,
date(in_time) dt
from
tb_user_log
union
select
uid,
date(out_time) dt
from
tb_user_log 
),t3 as(
   select
t1.uid new_uid,
new_dt,
case when datediff(dt,new_dt)=1 then t1.uid end retention_1d
from
t1 left join t2 on t1.uid=t2.uid and new_dt<dt 
where year(new_dt)=2021 and month(new_dt)=11
)

select
new_dt,
round(count(distinct retention_1d)/count(new_uid),2)
from
t3
group by new_dt