题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
思路:
1.先生成新用户表和用户活跃表
- 新用户表:时间为最早活跃的那一天,min()
- 用户活跃表:考虑隔天,使用UNION去重
2.联结两张表:
uid相同
a.date=date_sub(b.date,interval 1 day)
3. 从联立的表中,计算新用户留存率留存率:count(distinct b.uid)/count(distinct a.uid) -- 次日/前一天
条件:2021-11
按照日期分组
排序
select a.dt, round(count(b.uid)/count(a.uid),2) as uv_rate from -- 新用户表 ( select uid, min(date_format(in_time,"%Y-%m-%d")) as dt from tb_user_log group by uid ) a left join ( -- 用户活跃表 select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log union select uid, date_format(out_time,"%Y-%m-%d") as dt from tb_user_log ) b on a.uid=b.uid and a.dt=date_sub(b.dt,interval 1 day) where date_format(a.dt,"%Y-%m")= "2021-11" group by a.dt order by a.dt