题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
with T as( # 用户在某天活跃与下一次活跃 select uid, time, lead(time)over(partition by uid order by time) as next_time from ( select uid, date(in_time) as time, 1 as is_active from tb_user_log union select uid, date(out_time) as time, 1 as is_active from tb_user_log order by uid, time )t ), B as ( # 每日新用户有那些 select distinct date(min(in_time)over(partition by uid)) as first_log, uid from tb_user_log ) select B.first_log as dt, round(count(T.next_time)/count(*) , 2) as uv_left_rate from B left join T on B.first_log=T.time and B.uid=T.uid where date_format(B.first_log,'%Y-%m') = '2021-11' and (T.next_time is null or timestampdiff(day, time, next_time)=1 ) group by dt order by dt