题解 | #牛客每个人最近的登录日期(五)#

此题应该是目前牛客处理比较复杂的sql题了。

一、审题
“写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序。”
根据题目描述，次日留存率指新用户第二天登录的个数除以新用户第一天登录的个数。

二、解题
1、统计新用户第一天登录的次数
1）新用户，指最早日期登录的用户，如下

select 
  user_id, 
  min(date) 
from login
group by user_id

2）新用户第一天登录的个数，即初次登录的个数，根据1）获得的新用户最为删选条件，使用分组获得统计结果，如下

select
  date,
  sum(case when (user_id, date) in (
     select user_id, min(date) from login group by user_id
     ) then 1 else 0 end) as first_cnt
from login
group by date

结果：
+------------+-----------+
| date | first_cnt |
+------------+-----------+
| 2020-10-12 | 3 |
| 2020-10-13 | 0 |
| 2020-10-14 | 1 |
| 2020-10-15 | 0 |
+------------+-----------+
注意根据题目，未登录日期的新用户个数需要显示为0，所以使用综合sum聚合与case when条件运算，而非直接采用count计数。

2、新用户第二天登录的次数
1）找出新用户第二天登录的用户，需要采用date_add函数，即比最小日期大一天的日期则为第二天，

select 
  user_id, 
  date_add(min(date), interval 1 day) 
from login 
group by user_id

2）并指定为对应的日期用户为筛选条件，即可选择出新用户第二天登录的用户，同样使用sum聚合与case when 条件选择

select
  date_add(date, interval -1 day) as date, # 与步骤1结果的日期保持一致
  sum(case when (user_id, date) in (select user_id, date_add(min(date), interval 1 day) from login group by user_id) then 1 else 0 end) as second_cnt
from login
group by date

结果：
+------------+------------+
| date | second_cnt |
+------------+------------+
| 2020-10-12 | 0 |
| 2020-10-13 | 2 |
| 2020-10-14 | 0 |
| 2020-10-15 | 1 |
+------------+------------+

3、最后结果1和2的结果，将两表left join，由于新用户第一天登录的个数first_cnt可能出现为0的结果，因此采用ifnull函数，将null结果指定为0输出，因此最后结果如下

select 
  t1.date,
  ifnull(round(t2.second_cnt / t1.first_cnt, 3), 0) as p
from 
    (select
      date,
      sum(case when (user_id, date) in (
         select user_id, min(date) from login group by user_id
         ) then 1 else 0 end) as first_cnt
    from login
    group by date) t1
left join
    (select
      date_add(date, interval -1 day) as date,
      sum(case when (user_id, date) in (select user_id, date_add(min(date), interval 1 day) from login group by user_id) then 1 else 0 end) as second_cnt
    from login
    group by date) t2
on t1.date = t2.date
order by t1.date