题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
http://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
【新用户次日留存率】计算思路
1、先查询出个用户首次出现的日期frist_date，得到<新用户登录表>。使用group by，按uid分组，查找出min(dt)
2、再将<新用户登录表>与<用户活跃表>连接，使用left join。只有同一用户并且该用户第二天依旧登录才会保留记录，否则右表记录为空。因此连接条件需要满足2：
- 同一个用户：表1.uid=表2.uid
- 首日登录，第二天依然登录：date_add(表1.first_date,interval 1day)=表2.date
除解题思路以外，本题还有一个小难点，即如何计算：如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。需要进行并集操作，将登录时间和登出时间去并集，这里使用union，会去重。由此得到<用户活跃表>
综上，代码如下↓↓↓
select a.first_dt dt,round(count(b.uid)/count(a.uid),2) uv_left_rate from (select uid,date(min(in_time)) first_dt from tb_user_log GROUP BY uid) a LEFT JOIN (select uid,date(in_time) dt from tb_user_log UNION select uid,date(out_time) dt from tb_user_log )b on a.uid=b.uid and DATE_ADD(a.first_dt,INTERVAL 1 day)=b.dt where date_format(a.first_dt,'%Y-%m')='2021-11' GROUP BY a.first_dt order by dt ;
