SQL练习70：牛客每个人最近的登录日期(五)

题目链接：牛客网

题目描述
牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
。。。
第4行表示id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
。。
最后1行表示id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户

请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明:
2020-10-12登录了3个(id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667;
2020-10-13没有新用户登录，输出0.000;
2020-10-14登录了1个(id为4)新用户，2020-10-15，id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000;
2020-10-15没有新用户登录，输出0.000;
(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)

解法
题目要求获取每个日期新用户的次日留存率，直接获取很困难，我们需要这个问题一步步细化，先获取每个日期的新用户，再获取次日新用户的留存的信息，最后用某日期次日留存的用户数量/该日期新用户总数量。最后填补空值即可。
1.先获取新用户的user_id和他们的注册日期。

SELECT user_id, MIN(date) min_date
FROM login
GROUP BY user_id

user_id	min_date
2	2020-10-12
3	2020-10-12
1	2020-10-12
4	2020-10-14

2.获取次日留存的新用户user_id和注册时间min_date。

SELECT r1.user_id, r1.min_date
FROM (SELECT user_id, MIN(date) min_date
      FROM login
      GROUP BY user_id) r1, login l
WHERE r1.user_id = l.user_id AND l.date = DATE_ADD(r1.min_date, INTERVAL 1 DAY)

user_id	min_date
2	2020-10-12
1	2020-10-12
4	2020-10-14

3.用某日次日留存的用户数量/该日新用户总数量，即可得到新用户的次日留存率。

SELECT r2.min_date, round(COUNT(DISTINCT user_id)*1.0 / (SELECT COUNT(DISTINCT user_id) 
                                                         FROM (SELECT user_id, MIN(date) min_date
                                                               FROM login
                                                               GROUP BY user_id) r
                                                         WHERE r.min_date = r2.min_date), 3) p
FROM (SELECT r1.user_id, r1.min_date
      FROM (SELECT user_id, MIN(date) min_date
            FROM login
            GROUP BY user_id) r1, login l
      WHERE r1.user_id = l.user_id AND l.date = DATE_ADD(r1.min_date, INTERVAL 1 DAY)) r2
GROUP BY r2.min_date

min_date	p
2020-10-12	0.667
2020-10-14	1.000

4.通过ifnull函数填充空值。使用左外连接的方式将之前结果与login表进行连接。

SELECT l.date, ifnull(p, 0)
FROM login l LEFT JOIN (SELECT r2.min_date, round(COUNT(DISTINCT user_id)*1.0 / (SELECT COUNT(DISTINCT user_id) 
                                                                                 FROM (SELECT user_id, MIN(date) min_date
                                                                                       FROM login
                                                                                       GROUP BY user_id) r
                                                                                 WHERE r.min_date = r2.min_date), 3) p
                        FROM (SELECT r1.user_id, r1.min_date
                              FROM (SELECT user_id, MIN(date) min_date
                                    FROM login
                                    GROUP BY user_id) r1, login l
                              WHERE r1.user_id = l.user_id AND l.date = DATE_ADD(r1.min_date, INTERVAL 1 DAY)) r2
                        GROUP BY r2.min_date) R
ON l.date = R.min_date
GROUP BY l.date
ORDER BY l.date

date	p
2020-10-12	0.667
2020-10-13	0.000
2020-10-14	1.000
2020-10-15	0.000