SQL练习70:牛客每个人最近的登录日期5
题目链接:牛客网
题目描述
牛客每天有很多人登录,请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(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 |