题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
with t1 as(select uid,min(DATE(in_time)) as dt from tb_user_log group by uid), t2 as(select uid,date(in_time) as dt from tb_user_log UNION select uid,date(out_time) as dt from tb_user_log order by uid,dt), t3 as(select t1.* ,t2.dt as dt1 FROM t1 left join t2 on t1.uid=t2.uid and t1.dt=DATE_SUB(t2.dt,INTERVAL 1 day)) select dt,round(count(dt1)/count(dt),2) as uv_left_rate from t3 where DATE_FORMAT(dt,'%Y-%m')='2021-11' group by dt