题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
留存率的计算可以说是数据分析师, 或者数据开发必备的技能了, 很多公司都会用到这个指标, 该题还有进阶版的: 计算次日至7日内的新增用户留存率
- 思路: 根据uid分组, 取min(in_time)最小值, 得到用户的首次登录日期, 作为衍生表t1
select ul.uid, min(date(ul.in_time)) min_date from tb_user_log ul group by ul.uid
- 因为题目要求, out_time跨天也算该日活跃, 那么第二个子查询, 只需要关键字, uid, in_time, out_time即可, 这里其实可以直接用衍生表t1的uid左连接tb_user_log, 这样能得到如下的格式数据(假设, id为1的用户, 在1-3号都有登录过, 则会出现3条数据
t1.uid t1.min_date ul.uid ul.in_time ul.out_time 1 2022-11-01 1 2022-11-01 2022-11-01 1 2022-11-01 1 2022-11-02 2022-11-02 1 2022-11-01 1 2022-11-03 2022-11-03 - 如上表, 可以再根据, t1.min_date 进行分组, 对 t1.uid 进行去重, 则可得到该日的新增用户数, 而下一
天的用户数, 则可使用case when 对ul.in_time 和 out_time 进行判断, 这里我用的是count(distinct case when t2.l_in_time = date_add(t1.min_date, interval 1 day) or t2.l_out_time = date_add(t1.min_date, interval 1 day) then t2.uid else null end)
同理, 3-7日的留存率也可如此计算.优化点: 一个用户在一天可以登录多次
这样直接左关连tb_user_log(此表生产环境中一般都极大), 所以可以根据uid, date(ul.in_time), date(ul.out_time) 来缩小连表的数据量, 一个用户一天只需要保留一条数据即可
select t1.min_date as dt, round(count(distinct case when t2.l_in_time = date_add(t1.min_date, interval 1 day) or t2.l_out_time = date_add(t1.min_date, interval 1 day) then t2.uid else null end)/count(distinct t1.uid), 2) as uv_left_rate from ( select ul.uid, min(date(ul.in_time)) min_date from tb_user_log ul group by ul.uid ) t1 left join ( select ul.uid, date(ul.in_time) l_in_time, date(ul.out_time) l_out_time from tb_user_log ul ) t2 on t1.uid = t2.uid where min_date between '2021-11-01' and '2021-12-01' group by min_date order by min_date#数据分析师#