1.每日SQL----2024/11/7
题目：
计算用户次日留存率,即用户第二天继续登录的概率
表：
| id | device_id | date |
|---|---|---|
| 1 | 2138 | 2024-05-03 |
| 2 | 3214 | 2024-05-09 |
| 3 | 3214 | 2024-06-15 |
| 4 | 6543 | 2024-08-13 |
| 5 | 2315 | 2024-08-13 |
| 6 | 2315 | 2024-08-14 |
| 7 | 2315 | 2024-08-15 |
| 8 | 3214 | 2024-05-09 |
| 9 | 3214 | 2024-08-15 |
| 10 | 6543 | 2024-08-13 |
| 11 | 2315 | 2024-08-13 |
| 12 | 2315 | 2024-08-14 |
| 13 | 2315 | 2024-08-15 |
| 14 | 3214 | 2024-08-16 |
| 15 | 3214 | 2024-08-18 |
| 16 | 6543 | 2024-08-13 |
- id 用户唯一标识符
- device_id 用户登录使用的设备标识符
- date 用户登录日期
你应该返回的结果：
| avg_ret |
|---|
| 0.3000 |
请认真思考后作答
解题思路
-
去重数据：首先，我们需要从原始数据中选择每个设备ID (device_id) 和对应的登录日期 (date)，并去除重复项。这一步是为了确保每个设备在每个日期只有一条记录。
-
查找下一个登录日期：使用窗口函数 LEAD()，我们可以找到每个设备的下一个登录日期。LEAD() 函数会返回当前行之后的指定偏移量的行的值。这里我们将 LEAD() 应用在 date 列上，按 device_id 分区，并按 date 排序。
-
计算日期差：对于每一对连续的登录日期（当前日期 date1 和下一个日期 date2），我们使用 DATEDIFF() 函数计算它们之间的天数差。如果天数差为1，则表示用户在次日登录了。
-
计算次日留存率：使用 IF 语句，如果 DATEDIFF(date2, date1) = 1，则返回1，否则返回0。然后，对所有的这些0和1取平均值，得到次日留存率。
代码如下
select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (selectdistinct device_id,date as date1,lead(date) over (partition by device_id order by date) as date2from (select distinct device_id, datefrom user_detail) as uniq_id_date
) as id_last_next_date
运行结果如下
本题的重点是lead开窗函数的使用，你学会了吗？下期见~
后附建表语句
drop table if exists `user_detail`;
CREATE TABLE `user_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`date` date NOT NULL
);INSERT INTO user_detail VALUES(1,2138,'2024-05-03');
INSERT INTO user_detail VALUES(2,3214,'2024-05-09');
INSERT INTO user_detail VALUES(3,3214,'2024-06-15');
INSERT INTO user_detail VALUES(4,6543,'2024-08-13');
INSERT INTO user_detail VALUES(5,2315,'2024-08-13');
INSERT INTO user_detail VALUES(6,2315,'2024-08-14');
INSERT INTO user_detail VALUES(7,2315,'2024-08-15');
INSERT INTO user_detail VALUES(8,3214,'2024-05-09');
INSERT INTO user_detail VALUES(9,3214,'2024-08-15');
INSERT INTO user_detail VALUES(10,6543,'2024-08-13');
INSERT INTO user_detail VALUES(11,2315,'2024-08-13');
INSERT INTO user_detail VALUES(12,2315,'2024-08-14');
INSERT INTO user_detail VALUES(13,2315,'2024-08-15');
INSERT INTO user_detail VALUES(14,3214,'2024-08-16');
INSERT INTO user_detail VALUES(15,3214,'2024-08-18');
INSERT INTO user_detail VALUES(16,6543,'2024-08-13');