题解 | 计算用户的平均次日留存率
计算用户的平均次日留存率
https://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453
附上解析：
SELECT
ROUND(COUNT(DISTINCT b.device_id, b.date) / COUNT(DISTINCT a.device_id, a.date), 4) AS avg_ret
FROM (
SELECT DISTINCT device_id, DATE_FORMAT(date, '%Y-%m-%d') AS date
FROM question_practice_detail
) a
LEFT JOIN (
SELECT DISTINCT device_id, DATE_FORMAT(date, '%Y-%m-%d') AS date
FROM question_practice_detail
) b
ON a.device_id = b.device_id
AND b.date = DATE_ADD(a.date, INTERVAL 1 DAY);
========
关键步骤详解
1.数据去重处理（子查询a和b）
SELECT DISTINCT device_id, DATE_FORMAT(date, '%Y-%m-%d') AS date
FROM question_practice_detail
目的：确保每个用户每天只计一次活跃（避免同用户同天刷多题造成的重复计数）
示例数据中：
用户3214在2021-05-09有2条记录 → 去重后计1次
用户6543在2021-08-13有3条记录 → 去重后计1次
2.自连接逻辑：
LEFT JOIN ... ON a.device_id = b.device_id
AND b.date = DATE_ADD(a.date, INTERVAL 1 DAY)
a表：所有用户活跃日期（作为"第一天"）
b表：所有用户活跃日期（作为可能的"第二天"）
连接条件：
同一用户 (a.device_id = b.device_id)
b的日期是a的次日 (b.date = a.date + 1 day)
3.留存率计算
ROUND(COUNT(DISTINCT b.device_id, b.date) / COUNT(DISTINCT a.device_id, a.date), 4)
分子：次日仍活跃的用户数（满足连接条件的b表记录数）
分母：总活跃事件数（a表所有记录）
公式：留存率 = 次日活跃用户数 / 当日活跃用户数
============================
特殊处理说明
日期格式标准化：
DATE_FORMAT(date, '%Y-%m-%d') -- 确保时间部分不影响日期比较
跨月/年末处理：
DATE_ADD(a.date, INTERVAL 1 DAY) -- 自动处理月末（如2021-02-28→2021-03-01）
四舍五入：
ROUND(..., 4) -- 保留4位小数，符合示例输出要求
