题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
with a1 as( select uid, date(min(in_time)) as dt1 from tb_user_log group by uid ), a2 as ( select uid, date(in_time) as dt from tb_user_log ), a3 as ( select uid, date(out_time) as dt from tb_user_log ), a4 as ( select uid, dt from a2 union select uid, dt from a3 ), a5 as ( select uid, date_sub(dt, interval 1 day) as dt, uid*2 as num from a4 ), a6 as ( select a1.dt1 as dt, round(sum(case when num is not null then 1 else 0 end)/count(*),2) as uv_left_rate from a5 right join a1 on a1.uid = a5.uid and a1.dt1 = a5.dt group by a1.dt1 ) select dt, uv_left_rate from a6 where dt>= '2021-11-01' and dt<='2021-11-30' order by dt