SQL29 计算用户的平均次日留存率

SQL29 计算用户的平均次日留存率

计算用户的平均次日留存率_牛客题霸_牛客网

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的留存率。

---

示例：question_practice_detail

-- 输入：
DROP TABLE IF EXISTS `question_practice_detail`;
CREATE TABLE `question_practice_detail` (`id` int NOT NULL,`device_id` int NOT NULL,`question_id`int NOT NULL,`result` varchar(32) NOT NULL,`date` date NOT NULL
);

-- 插入数据：
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');

-- 输出：
avg_ret
0.3000

-- 方法一：
SELECTCOUNT(DISTINCT t2.device_id, t2.date) / COUNT(DISTINCT t1.device_id, t1.date) AS avg_ret 
FROMquestion_practice_detail AS t1
LEFT JOIN question_practice_detail AS t2
ON t1.device_id = t2.device_idAND DATEDIFF(t2.date, t1.date) = 1;

 【解题思路】
1）用 datediff 区分第一天和第二天在线的 device_id
2）用 left join 做自表联结
3）用 distinct t2.device_id, t2.date 做双重去重，找到符合条件的当天在线人数

-- 方法二：
SELECT(t1.num / t2.num) AS avg_ret
FROM( -- 计算第二天再来的记录数量SELECTCOUNT(DISTINCT a.device_id, a.date) AS numFROMquestion_practice_detail aINNER JOIN question_practice_detail b ON a.device_id = b.device_idAND a.date = DATE_ADD(b.date, INTERVAL 1 DAY)) t1,( -- 计算总记录数量SELECTCOUNT(DISTINCT device_id, date) AS numFROMquestion_practice_detail) t2;

【解题思路】
留存率=(去重后的用户有连续两天刷题记录次数)/(去重日期用户后刷题记录次数)
1）计算第二天再来的记录数量 (t1)
使用自连接查找在某一日期出现的设备 ID，且该设备 ID 在前一天也有记录，计算这些设备 ID 和日期组合的唯一数量。
2）计算总记录数量 (t2)
计算 question_practice_detail 表中所有唯一的设备 ID 和日期组合数量。
3）最终计算
将 t1 的结果（第二天再来的设备数量）除以 t2 的结果（总的设备和日期组合数量），得到平均值 avg_ret。

【总结】

1）COUNT()函数

COUNT()函数是一个聚合函数，用于计算表中行的数量或特定列的非空值数量。
COUNT(*) 计算表中的所有行，包括 NULL 值
COUNT(column_name) 只计算该列中的 非NULL 值
COUNT(DISTINCT column_name) 计算该列中 唯一且 非NULL 的值，即该列的不同值的数目
count(distinct a.device_id, a.date) as num
计算在 question_practice_detail 表中不同设备(device_id)和日期(date)组合的数量。

2）日期加减函数

DATE_SUB(date, interval 1 day) 从给定的 date 中减去指定的时间间隔
DATE_ADD(date, interval -1 day) 向给定的 date 中加上指定的时间间隔(加上-1天，即减去1天)
DATEDIFF(date2, date1) = 1 返回 date2 - date1 之间的天数差(天数差1天)

示例
DATE_SUB('2025-03-10', interval 1 day) 将返回 '2025-03-09'。
DATE_ADD('2025-03-10', interval -1 day) 也将返回 '2025-03-09'。
DATEDIFF('2025-03-10', '2025-03-09') 将返回 1，表示两天之间相差1天。

【参考文献】
1、https://blog.csdn.net/zhanchulan/article/details/140047896
2、https://blog.csdn.net/qq_43543789/article/details/142854428
3、https://blog.csdn.net/2301_76352996/article/details/142143839

【附录】

原本日期

减1后日期
date_sub(date, interval 1 day)

left join 后情况（表关联后计数注意NULL值）