网易大数据面试SQL-游戏玩家留存率
⚠️ 待修正
一、题目背景
这道题来自网易的数据分析岗面试。网易是游戏和音乐，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：用户粘性相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张玩家登录日志表 t1_player_login，记录了每个玩家每天的登录情况。请计算每日新增用户的次日留存率、3日留存率和7日留存率。
说明：
- 次日留存率 = 某日新增用户中第二天仍登录的用户数 / 该日新增用户数
- 3日留存率 = 某日新增用户中第3天仍登录的用户数 / 该日新增用户数
玩家登录表 t1_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 t1_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 |
+-----------+------------------+
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 t1_player_login group by player_id) t1
left join t1_player_login t2
on t1.player_id = t2.player_id
and datediff(t2.login_date, t1.first_login_date) = 1
left join t1_player_login t3
on t1.player_id = t3.player_id
and datediff(t3.login_date, t1.first_login_date) = 2
left join t1_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.0000 | 0.0000 | 0.0000 |
| 2024-01-04 | 1 | 0 | 0 | 0 | 0.0000 | 0.0000 | 0.0000 |
+------------------+-----------+---------------+----------------+----------------+------------+------------+------------+
五、常见坑点
坑1：最近日期的新用户无法计算留存 — 最后一天新增用户尚未有"次日"，留存率=0/NULL，实际应用需排除最近N天。
坑2：LEFT JOIN条件不完整导致虚高 — 必须同时匹配 user_id 和 date，只匹配 user_id 会把隔天也算成次日留存。
六、举一反三
-
3日/7日/30日留存：DATE_ADD偏移量从1改为3/7/30，一次算出多窗口留存
-
按渠道计算留存质量：GROUP BY加channel字段，对比不同获客渠道的用户留存
-
留存曲线：对每个首次登录日在后续1-30天分别LEFT JOIN，画出完整留存衰减趋势
七、知识点总结
| 考点 | 说明 |
|---|---|
| LAG / LEAD | 获取前/后一行数据，用于环比计算、状态变更检测 |
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_player_login (
player_id string COMMENT '玩家ID',
login_date string COMMENT '登录日期'
) COMMENT '玩家登录日志表';
-- 数据插入
INSERT INTO t1_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真题
