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

with t1 as
(select uid, dt, rank() over(partition by uid order by date(dt)) as ranking
from (select uid,date(in_time) as dt from tb_user_log
 union 
 select uid,date(out_time)as dt from tb_user_log)t

group by uid,dt)

select t1.dt,
round(sum(case when t2.ranking=2 then 1 else 0 end)/sum(case when t1.ranking =1 then 1 else 0 end),2) as uv_left_rate
from t1 left join t1 as t2 on t1.dt=t2.dt-1 and t1.uid=t2.uid
 where date_format(t1.dt,'%Y%m') ='202111'
group by t1.dt
having uv_left_rate is not null
order by t1.dt

这个题只要求了一个次日留存，就想使用窗口函数+自联结解决了。

留存率的写法还有多种。

该题重点，开始时间和结束时间都算入活跃。
筛选是在最后筛选出11月份的。