字节跳动大数据面试SQL-新增用户数及其留存率
一、题目背景
这道题是第9题"次日留存率"的进阶版。字节跳动面试官经常在候选人写完次日留存后追问："如果我要看3日、7日留存呢？你写9个 UNION ALL？"——这其实是考察"能不能用一次扫描算出多日留存"。
业务场景：产品上线了新功能，需要看"这个功能吸引来的新用户，在第1/3/7/30天的留存率分别是多少"，以此评估功能的长期价值。
二、题目
用户登录日志表 t12_zj_user_login 同第9题。请计算每天的新增用户数，以及他们的次日、3日、7日留存率。
t12_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-03 |
| 1 | 2025-01-04 |
| 4 | 2025-01-03 |
| 4 | 2025-01-04 |
| 4 | 2025-01-05 |
| 5 | 2025-01-03 |
| 3 | 2025-01-05 |
| 5 | 2025-01-07 |
+----------+-------------+
期望输出：每天的新增用户数、次日留存率、3日留存率、7日留存率。
三、思路分析
核心与第9题一样，但需要在 LEFT JOIN 时一次关联多个偏移天数：
- 找每个用户首次登录日
MIN(login_date) - 使用case when 计算第1、第3、7日留存用户数
- 同时 COUNT 多个偏移的 DISTINCT user_id
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：找首次登录
SELECT user_id, MIN(login_date) AS first_date
FROM t12_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-03 |
| 5 | 2025-01-04 |
+----------+-------------+
5 rows selected (10.96 seconds)(https://www.dwsql.com/)
步骤2：一次 LEFT JOIN 通过计算时间偏移来关联多个偏移天数
with first_login as (
select user_id, min(login_date) as first_date
from t12_zj_user_login
group by user_id
)
select
f.first_date,
count(distinct f.user_id) as new_users,
count(distinct case when date_diff(d1.login_date,f.first_date) = 1 then d1.user_id end) as day1_retained,
count(distinct case when date_diff(d1.login_date,f.first_date) = 2 then d1.user_id end) as day3_retained,
count(distinct case when date_diff(d1.login_date,f.first_date) = 6 then d1.user_id end) as day7_retained,
round(count(distinct case when date_diff(d1.login_date,f.first_date) = 1 then d1.user_id end) / count(distinct f.user_id), 2) as day1_pct,
round(count(distinct case when date_diff(d1.login_date,f.first_date) = 2 then d1.user_id end) / count(distinct f.user_id), 2) as day3_pct,
round(count(distinct case when date_diff(d1.login_date,f.first_date) = 6 then d1.user_id end) / count(distinct f.user_id), 2) as day7_pct
from first_login f
left join t12_zj_user_login d1
on f.user_id = d1.user_id
where d1.login_date > f.first_date
group by f.first_date
order by f.first_date;
最终结果：
+-------------+------------+----------------+----------------+----------------+-----------+-----------+-----------+
| first_date | new_users | day1_retained | day3_retained | day7_retained | day1_pct | day3_pct | day7_pct |
+-------------+------------+----------------+----------------+----------------+-----------+-----------+-----------+
| 2025-01-01 | 3 | 1 | 1 | 1 | 0.33 | 0.33 | 0.33 |
| 2025-01-03 | 1 | 1 | 1 | 0 | 1.0 | 1.0 | 0.0 |
| 2025-01-04 | 1 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 |
+-------------+------------+----------------+----------------+----------------+-----------+-----------+-----------+
3 rows selected (1.023 seconds)(https://www.dwsql.com)
分析：1月1日新增3人，次日仅1人回来(33%)，但第3天有2人回来了(67%)——说明部分用户不是每天都打开，但3天内会再回来。
五、常见坑点
坑1：多 JOIN 性能问题
3 个 LEFT JOIN 各扫一次全表，对于亿级数据会非常慢。这里用 case when 计算每个偏移天数的留存用户数，避免了多次扫描全表。where条件限制d1.login_date > f.first_date，只关联后续登录记录。
坑2：最近7天无法计算7日留存
如1月5日的新增用户，7天后是1月12日——如果数据只到1月8日，7日留存率是 0。实际业务中通常只展示"距今至少N天前"的新增用户。
坑3：N日留存与首次登录日时间间隔
3日留存是在用户首次登录日后的第3天登录，而不是在+3天登录。7日留存是在用户首次登录日后的第7天登录，而不是在+7天登录。
坑4：区分N日留存与N日内留存 有些题目让计算N日内留存，而不是N日留存。
六、举一反三
- 透视表形式：用
SUM(CASE WHEN DATEDIFF = 1 THEN 1 END)替代多次 LEFT JOIN，一次 GROUP BY 透视所有天数 - 留存曲线 SQL：用一个日期维度表 CROSS JOIN 偏移天数(1-30)，左关联登录表，一条 SQL 画出完整留存曲线
七、知识点总结
| 考点 | 说明 |
|---|---|
| 多表 LEFT JOIN | 一次关联多个偏移天数，代码简洁 |
| 多个 COUNT DISTINCT | 不同别名的 user_id 互不干扰 |
| 性能考量 | 大数据量优先用透视表方案替代多次 JOIN |
| 数据时效 | 最近N天的新增用户无法计算N日留存 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE IF NOT EXISTS t12_zj_user_login (
user_id INT,
login_date STRING
);
INSERT INTO t12_zj_user_login VALUES
(1, '2025-01-01'),
(2, '2025-01-01'),
(3, '2025-01-01'),
(1, '2025-01-02'),
(2, '2025-01-03'),
(1, '2025-01-04'),
(4, '2025-01-03'),
(4, '2025-01-04'),
(4, '2025-01-05'),
(5, '2025-01-04'),
(3, '2025-01-07'),
(5, '2025-01-07');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
