题解 | #统计一下牛客每个日期新用户的次日留存率#

# 想法1
select a.date, 
       #sum(case when a.date = m.firstDate then 1 else 0 end) cn_new,
       #sum(case when n.user_id is not null then 1 else 0 end) cn_liv
       ifnull(
           round(
           sum(case when n.user_id is not null then 1 else 0 end) /
           sum(case when a.date = m.firstDate then 1 else 0 end)
           , 3
       ), 0.000) as p
  from login a
  left join (select user_id, min(date) firstDate 
               from login
              group by user_id) m
    on a.user_id = m.user_id
   and a.date = m.firstDate
  left join login n
    on m.user_id = n.user_id
   and m.firstDate = date_add(n.date, interval -1 day)
 group by a.date
 order by date;          

想法的逻辑
a表：为了取全部日期，故使用login表的日期做结果展示（后续采用左关联）；
m表：取各用户第一次登录的日期，可以通过此日期取出当日新用户数cn_new；
n表：在login表找出新用户次日登录数据，即m表数据中在次日有登录的记录；
该逻辑下12日数据举例应为：

在m和n分别统计数据量，就得到了分母cn_new：“12日新入网用户数”，分子cn_liv：“12日新入网用户的次日留存用户数”，两个相除保留3位小数即为结果。另，有分母为0的情况，一开始依旧是用case when实现，看了排行才知道有ifnull函数，故有最终语句，如上。


注：#为注释部分，做测试使用，检查分子分母与预期一致。