SQL 游戏玩家留存率：次日/3日/7日留存（网易面试题）
一、题目背景
这道题来自网易游戏事业部的用户增长数据分析岗面试。留存率是衡量游戏"粘性"的北极星指标——DAU 只告诉你有多少人来了，留存率才告诉你多少人留下了。次日留存反映新用户首日体验质量，3日留存反映核心玩法吸引力，7日留存反映长期留存潜力。
业务场景：增长团队每天监控新增用户的次日/3日/7日留存曲线。如果某天新增用户的留存率突然下滑，可能意味着当天的渠道投放质量有问题（刷量或非目标用户）。这道 SQL 就是留存率日报的核心查询。
二、题目
现有一张玩家登录日志表 t4_player_login，记录了每个玩家每天的登录情况。请计算每日新增用户的次日留存率、3日留存率和7日留存率。
说明：
- 次日留存率 = 某日新增用户中第二天仍登录的用户数 / 该日新增用户数
- 3日留存率 = 某日新增用户中第3天仍登录的用户数 / 该日新增用户数
玩家登录表 t4_player_login：
+------------+-------------+
| player_id | login_date |
+------------+-------------+
| P001 | 2024-01-01 |
| P002 | 2024-01-01 |
| P003 | 2024-01-01 |
| P001 | 2024-01-02 |
| P002 | 2024-01-02 |
| P004 | 2024-01-02 |
| P003 | 2024-01-03 |
| P001 | 2024-01-03 |
| P004 | 2024-01-03 |
| P002 | 2024-01-04 |
| P005 | 2024-01-04 |
| P001 | 2024-01-07 |
| P003 | 2024-01-08 |
+------------+-------------+
三、思路分析
本题考察留存率计算，需要先找出每个用户的首次登录日期（即新增日期），再与后续登录记录进行关联。
解题步骤：
- 对每个玩家按
login_date取最小值作为first_login_date（新增日期）； - 将新增表与登录明细表 LEFT JOIN，关联条件为
player_id相同，且登录日期与首次登录日期的差值为 1、3、7 天； - 按新增日期分组，统计新增用户数和各留存天数对应的留存用户数；
- 计算留存率。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 找出每个玩家的首次登录日期
执行SQL
select player_id,
min(login_date) as first_login_date
from t4_player_login
group by player_id
执行结果
+------------+-------------------+
| player_id | first_login_date |
+------------+-------------------+
| P001 | 2024-01-01 |
| P002 | 2024-01-01 |
| P003 | 2024-01-01 |
| P004 | 2024-01-02 |
| P005 | 2024-01-04 |
+------------+-------------------+
5 rows selected (0.375 seconds)(https://www.dwsql.com)
2. 计算留存率
执行SQL
select t1.first_login_date,
count(distinct t1.player_id) as new_users,
count(distinct t2.player_id) as day1_retained,
count(distinct t3.player_id) as day3_retained,
count(distinct t4.player_id) as day7_retained,
round(count(distinct t2.player_id) / count(distinct t1.player_id), 4) as day1_rate,
round(count(distinct t3.player_id) / count(distinct t1.player_id), 4) as day3_rate,
round(count(distinct t4.player_id) / count(distinct t1.player_id), 4) as day7_rate
from (select player_id, min(login_date) as first_login_date
from t4_player_login group by player_id) t1
left join t4_player_login t2
on t1.player_id = t2.player_id
and datediff(t2.login_date, t1.first_login_date) = 1
left join t4_player_login t3
on t1.player_id = t3.player_id
and datediff(t3.login_date, t1.first_login_date) = 2
left join t4_player_login t4
on t1.player_id = t4.player_id
and datediff(t4.login_date, t1.first_login_date) = 6
group by t1.first_login_date
order by t1.first_login_date
执行结果
+-------------------+------------+----------------+----------------+----------------+------------+------------+------------+
| first_login_date | new_users | day1_retained | day3_retained | day7_retained | day1_rate | day3_rate | day7_rate |
+-------------------+------------+----------------+----------------+----------------+------------+------------+------------+
| 2024-01-01 | 3 | 2 | 2 | 1 | 0.6667 | 0.6667 | 0.3333 |
| 2024-01-02 | 1 | 1 | 0 | 0 | 1.0 | 0.0 | 0.0 |
| 2024-01-04 | 1 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 |
+-------------------+------------+----------------+----------------+----------------+------------+------------+------------+
3 rows selected (1.256 seconds)(https://www.dwsql.com)
五、常见坑点
坑1：最近日期的新用户无法计算留存
最后 N 天新增的用户还没有"第 N+1 天"的数据（如最后一天新增的用户，次日数据还不存在）。如果不过滤，留存率会是 0，拉低整体数据。实际应用中需排除最近 N 天的新用户，确保每个用户都有完整的留存观察窗口。
坑2：LEFT JOIN 关联条件不完整导致留存虚高
关联条件必须同时匹配 player_id 和 login_date，只匹配 player_id 会把隔了好几天才回来的用户也算成"次日留存"，留存率严重虚高。必须严格限定日期差 = 1（次日）、= 2（3日）、= 6（7日）。
坑3：DATEDIFF 计算 3日留存的偏移量
DATEDIFF(login_date, first_login_date) = 2 才表示"第3天回访"，因为 DATEDIFF(次日, 首日) = 1。很多人会误把 3日留存的偏移量写成 3，实际应该是 2（首日+2天=第3天）。同理，7日留存的偏移量是 6 而非 7。
六、举一反三
-
3日/7日/30日留存一并输出：多次 LEFT JOIN 登录表，分别关联 datediff = 1/2/6/29，一行输出次日/3日/7日/30日四个留存率，用于留存曲线分析
-
按渠道/版本对比留存质量：GROUP BY 加入
channel/app_version字段，对比不同获客渠道或 App 版本的新用户留存差异，定位"哪个渠道来的用户质量最差" -
留存衰减曲线：对每个
first_login_date在后续 1-30 天分别 COUNT DISTINCT，画出一条完整的留存衰减曲线，观察留存率在哪天出现断崖式下跌（通常是第3天或第7天） -
分期留存率：不用固定的次日/3日/7日，而是按日期每隔 7 天取一个留存快照，做"每周同期留存率对比"，识别留存率周度变化趋势
七、知识点总结
| 考点 | 说明 |
|---|---|
| MIN + GROUP BY 找首日 | MIN(login_date) 按 player_id 分组获取每个玩家的新增日期 |
| 多次 LEFT JOIN + DATEDIFF | 分别 JOIN 次日(DATEDIFF=1)、3日(=2)、7日(=6) 的登录记录 |
| COUNT DISTINCT 分子分母 | 新增用户和留存用户都需要去重，一人一天只计一次 |
| 留存窗口完整性 | 排除最近 N 天的新用户，确保每个用户有完整的留存观察期 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t4_player_login (
player_id string COMMENT '玩家ID',
login_date string COMMENT '登录日期'
) COMMENT '玩家登录日志表';
-- 数据插入
INSERT INTO t4_player_login VALUES
('P001', '2024-01-01'),
('P002', '2024-01-01'),
('P003', '2024-01-01'),
('P001', '2024-01-02'),
('P002', '2024-01-02'),
('P004', '2024-01-02'),
('P003', '2024-01-03'),
('P001', '2024-01-03'),
('P004', '2024-01-03'),
('P002', '2024-01-04'),
('P005', '2024-01-04'),
('P001', '2024-01-07'),
('P003', '2024-01-08');
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
