题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
http://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
问题分析
统计2021年11月每天新用户的次日留存率（保留2位小数）。注：1、次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。2、如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。
1、2021年11月份每天——使用where过滤出2021年11月份的数据；
1、2021年11月份每天——使用where过滤出2021年11月份的数据；
2、新用户——以用户为分组，使用min()函数计算出最小活跃日期，这样也就得到了所有有新增用户的日期和对应新增的uid
3、次日留存率——将新用户的最小活跃日期+1后再与原表进行左连接，根据次日留存率的定义，跨天按连续两天活跃处理，因此，连接条件是最小活跃日期+1与原表的in_date或out_date相等且uid相等
知识点
1、year()函数：获取日期对应的年份，如：2021
2、month()函数：获取日期对应的月份，如：11
3、min()函数：求最小值
4、adddate(date,interval 1 day)函数：将日期date增加1天
5、子查询：也称临时表，本解法运用了多个子查询嵌套和连接
6、with 语句：mysql8.0开始支持with语句，可以简化代码
答案参考
WITH min_dt AS (SELECT min(date(in_time))dt,uid FROM tb_user_log GROUP BY uid)#查找出每天的新用户 SELECT b.dt,round(b.uid_cnt/c.uid_cnt,2)uv_left_rate FROM (SELECT subdate(dt_add,interval 1 day)dt,count(distinct tb.uid)uid_cnt FROM (SELECT adddate(dt,interval 1 day)dt_add,uid FROM min_dt)a LEFT JOIN tb_user_log tb ON ((date(in_time)=dt_add OR date(out_time)=dt_add) AND a.uid=tb.uid) GROUP BY dt )b RIGHT JOIN (SELECT dt,count(uid)uid_cnt FROM min_dt GROUP BY dt)c USING(dt) WHERE year(b.dt)=2021 AND month(b.dt)=11 ORDER BY dt;
运行结果
查看25道真题和解析
