字节跳动大数据面试SQL-用户的第二天的留存率
一、题目背景
这道题来自字节跳动用户增长部门的数据分析岗面试。留存率是衡量产品"粘性"的核心指标——用户今天来了，明天还会不会再来？对于抖音、今日头条这样的内容产品，次日留存率直接反映了推荐算法的质量和内容的吸引力。
业务场景：DAU 只能告诉你有多少人来过，留存率才能告诉你有多少人留下来了。投资人看留存，产品经理盯留存，面试官也爱考留存。
二、题目
现有一张用户登录日志表 t9_zj_user_login，记录了用户每天的登录情况（每天每个用户至多一条记录）。请计算每天的新增用户的次日留存率。
t9_zj_user_login 表
+----------+------------+
| user_id | login_date |
+----------+------------+
| 1 | 2025-01-01 |
| 2 | 2025-01-01 |
| 3 | 2025-01-01 |
| 1 | 2025-01-02 |
| 2 | 2025-01-02 |
| 4 | 2025-01-02 |
| 5 | 2025-01-02 |
| 1 | 2025-01-03 |
| 3 | 2025-01-03 |
| 4 | 2025-01-03 |
+----------+------------+
定义：
- 新增用户：某天首次登录的用户（之前从未出现过）
- 次日留存：新增用户在第二天再次登录
- 次日留存率 = 次日留存的用户数 / 新增用户数
三、思路分析
核心是"锚定首次登录日期" + "判断次日是否登录"，分三步：
- 找首次登录日：
MIN(login_date) GROUP BY user_id确定每个用户是哪天新增的 - 关联次日登录：LEFT JOIN 原表，关联条件为
同用户 AND 登录日 = 首次登录日 + 1 - 计算留存率：按首次登录日分组，
COUNT(次日user_id) / COUNT(新增user_id)
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：找出每个用户的首次登录日期
SELECT
user_id,
MIN(login_date) AS first_date
FROM t9_zj_user_login
GROUP BY user_id;
+----------+-------------+
| user_id | first_date |
+----------+-------------+
| 1 | 2025-01-01 |
| 2 | 2025-01-01 |
| 3 | 2025-01-01 |
| 4 | 2025-01-02 |
| 5 | 2025-01-02 |
+----------+-------------+
5 rows selected (0.701 seconds)
1月1日新增用户1/2/3，1月2日新增用户4/5。
步骤2：关联次日登录记录
用首次登录表 LEFT JOIN 登录日志表，关联条件为同用户且登录日为首次登录日+1。
WITH first_login AS (
SELECT user_id, MIN(login_date) AS first_date
FROM t9_zj_user_login
GROUP BY user_id
)
SELECT
f.first_date,
f.user_id AS new_user,
t.user_id AS retained_user
FROM first_login f
LEFT JOIN t9_zj_user_login t
ON f.user_id = t.user_id
AND t.login_date = DATE_ADD(f.first_date, 1);
执行结果：
+-------------+-----------+----------------+
| first_date | new_user | retained_user |
+-------------+-----------+----------------+
| 2025-01-01 | 1 | 1 |
| 2025-01-01 | 2 | 2 |
| 2025-01-01 | 3 | NULL |
| 2025-01-02 | 4 | 4 |
| 2025-01-02 | 5 | NULL |
+-------------+-----------+----------------+
5 rows selected (0.625 seconds)
retained_user 为 NULL 表示该用户次日未登录。
步骤3：聚合计算留存率
WITH first_login AS (
SELECT user_id, MIN(login_date) AS first_date
FROM t9_zj_user_login
GROUP BY user_id
)
SELECT
f.first_date AS date,
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT t.user_id) AS day1_retained,
ROUND(COUNT(DISTINCT t.user_id) * 100.0 / COUNT(DISTINCT f.user_id), 1) AS day1_retention_pct
FROM first_login f
LEFT JOIN t9_zj_user_login t
ON f.user_id = t.user_id
AND t.login_date = DATE_ADD(f.first_date, 1)
GROUP BY f.first_date
ORDER BY f.first_date;
最终结果：
+-------------+------------+----------------+---------------------+
| date | new_users | day1_retained | day1_retention_pct |
+-------------+------------+----------------+---------------------+
| 2025-01-01 | 3 | 2 | 66.7 |
| 2025-01-02 | 2 | 1 | 50.0 |
+-------------+------------+----------------+---------------------+
2 rows selected (1.419 seconds)
1月1日新增3人，次日回来2人，留存率 66.7%。用户3虽然在1月3日又登录了，但不满足"次日"条件，不算次日留存。
五、常见坑点
坑1：LEAD 方案的隐蔽陷阱
有人用 LEAD(login_date) OVER (PARTITION BY user_id ORDER BY login_date) 判断次日是否登录。这个方法的问题是：如果用户只登录过一次，LEAD 返回 NULL，看起来像是"没留存"——这倒没错。但 LEAD 方案需要先获取全量登录记录再做窗口，代码更复杂，不如 JOIN 方案直观。
坑2：不能忘记 COUNT DISTINCT
虽然本例每天每用户只有一条记录，但实际数据可能有多条（如多设备登录）。用 COUNT(DISTINCT user_id) 更安全。
坑3：最近日期无法计算留存
最后一天（如1月3日）新增的用户，还没有"次日"数据，留存率会是 0 或 NULL。实际业务中通常排除最近N天的新增用户。
六、举一反三
- 3日/7日留存：将
DATE_ADD(first_date, 1)改为3或7，可一次算出多日留存 - 按渠道维度：如果登录表有
channel字段，GROUP BY 加上渠道，可对比不同渠道的留存质量 - 留存曲线：写一个循环生成 DATE_ADD 1-30 天的 LEFT JOIN，画出完整的留存衰减趋势图
七、知识点总结
| 考点 | 说明 |
|---|---|
| MIN + GROUP BY | 锚定首次登录日期作为"新增日" |
| LEFT JOIN + DATE_ADD | 判断次日是否留存，NULL = 未留存 |
| COUNT DISTINCT | 去重避免多次登录干扰 |
| 留存率公式 | COUNT(次日user_id) * 100.0 / COUNT(新增user_id) |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE IF NOT EXISTS t9_zj_user_login (
user_id INT,
login_date STRING
);
INSERT INTO t9_zj_user_login VALUES
(1, '2025-01-01'),
(2, '2025-01-01'),
(3, '2025-01-01'),
(1, '2025-01-02'),
(2, '2025-01-02'),
(4, '2025-01-02'),
(5, '2025-01-02'),
(1, '2025-01-03'),
(3, '2025-01-03'),
(4, '2025-01-03');
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
