腾讯大数据面试SQL-新用户次日留存率
⚠️ 待修正
一、题目背景
这道题来自腾讯微信/QQ的用户增长数据分析岗面试。留存率是衡量产品"粘性"的北极星指标——DAU只告诉你有多少人来过，留存率才告诉你多少人留下来了。微信的次日留存率常年维持在极高水平，是产品体验的最好证明。
业务场景：增长团队每天监控新增用户的次日/3日/7日留存曲线。如果某天新增用户的留存率突然下滑，可能意味着当天的渠道投放质量有问题（刷量/非目标用户）。这道SQL就是留存率报表的基础查询。
二、题目
现有用户登录日志表 t10_login_log，记录了用户每次登录的日期。请计算每日新增用户的次日留存率。
次日留存率 = 当日新增用户中，第二天（次日）再次登录的用户数 / 当日新增用户数
用户登录日志表 t10_login_log：
+----------+-------------+
| user_id | login_date |
+----------+-------------+
| u01 | 2023-03-01 |
| u02 | 2023-03-01 |
| u03 | 2023-03-01 |
| u01 | 2023-03-02 |
| u02 | 2023-03-02 |
| u04 | 2023-03-02 |
| u05 | 2023-03-02 |
| u01 | 2023-03-03 |
| u03 | 2023-03-03 |
| u04 | 2023-03-03 |
| u05 | 2023-03-03 |
| u06 | 2023-03-03 |
| u07 | 2023-03-03 |
| u01 | 2023-03-04 |
| u02 | 2023-03-04 |
| u06 | 2023-03-04 |
| u07 | 2023-03-04 |
+----------+-------------+
三、思路分析
本题是数据分析中非常经典的"留存率"计算问题，核心思路如下：
- 确定每个用户的首次登录日期（即新增日期）：使用
MIN() OVER()窗口函数或将MIN(login_date)按user_id分组； - 判断用户是否在次日也登录了：将用户登录表与自身进行 LEFT JOIN，连接条件为同一用户且登录日期 = 新增日期 + 1天；
- 按新增日期分组计算留存率：次日留存用户数 / 新增用户总数；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 计算每个用户的首次登录日期
执行SQL
select user_id,
login_date,
min(login_date) over (partition by user_id) as first_login_date
from t10_login_log
执行结果
+----------+-------------+------------------+
| user_id | login_date | first_login_date |
+----------+-------------+------------------+
| u01 | 2023-03-01 | 2023-03-01 |
| u01 | 2023-03-02 | 2023-03-01 |
| u01 | 2023-03-03 | 2023-03-01 |
| u01 | 2023-03-04 | 2023-03-01 |
| u02 | 2023-03-01 | 2023-03-01 |
| u02 | 2023-03-02 | 2023-03-01 |
| u02 | 2023-03-04 | 2023-03-01 |
| u03 | 2023-03-01 | 2023-03-01 |
| u03 | 2023-03-03 | 2023-03-01 |
| u04 | 2023-03-02 | 2023-03-02 |
| u04 | 2023-03-03 | 2023-03-02 |
| u05 | 2023-03-02 | 2023-03-02 |
| u05 | 2023-03-03 | 2023-03-02 |
| u06 | 2023-03-03 | 2023-03-03 |
| u06 | 2023-03-04 | 2023-03-03 |
| u07 | 2023-03-03 | 2023-03-03 |
| u07 | 2023-03-04 | 2023-03-03 |
+----------+-------------+------------------+
2. 计算每日新增用户数
执行SQL
select first_login_date,
count(distinct user_id) as new_user_cnt
from (
select user_id,
min(login_date) over (partition by user_id) as first_login_date
from t10_login_log
) t
group by first_login_date
执行结果
+------------------+--------------+
| first_login_date | new_user_cnt |
+------------------+--------------+
| 2023-03-01 | 3 |
| 2023-03-02 | 2 |
| 2023-03-03 | 2 |
+------------------+--------------+
3. 计算次日留存用户数及留存率
执行SQL
select t1.first_login_date,
count(distinct t1.user_id) as new_user_cnt,
count(distinct t2.user_id) as retain_user_cnt,
round(count(distinct t2.user_id) / count(distinct t1.user_id), 4) as retain_rate
from (
select user_id,
min(login_date) over (partition by user_id) as first_login_date
from t10_login_log
) t1
left join (
select user_id,
login_date
from t10_login_log
) t2
on t1.user_id = t2.user_id
and t2.login_date = date_add(t1.first_login_date, 1)
group by t1.first_login_date
order by t1.first_login_date
执行结果
+------------------+--------------+-----------------+-------------+
| first_login_date | new_user_cnt | retain_user_cnt | retain_rate |
+------------------+--------------+-----------------+-------------+
| 2023-03-01 | 3 | 2 | 0.6667 |
| 2023-03-02 | 2 | 2 | 1.0000 |
| 2023-03-03 | 2 | 2 | 1.0000 |
+------------------+--------------+-----------------+-------------+
结果解读：3月1日新增用户3人（u01,u02,u03），次日（3月2日）有2人再次登录（u01,u02），次日留存率 = 2/3 ≈ 66.67%
五、常见坑点
坑1：最近一天的新用户无法计算留存
3月4日新增的用户，还没有"次日"（3月5日）的数据，留存率 = 0/NULL。实际业务中通常排除最近N天的新用户，确保留存窗口完整。
坑2：LEFT JOIN 关联条件写错导致留存虚高
关联条件必须同时匹配 user_id 和 login_date，如果只匹配 user_id，会把隔了好几天才回来的用户也算成"次日留存"，留存率虚高。
坑3：MIN OVER vs GROUP BY 方案的选择
MIN(login_date) OVER (PARTITION BY user_id) 保留了所有行便于后续 JOIN，GROUP BY user_id 方案只保留一行更简洁。如果后续还要关联其他维度，前者更方便。
六、举一反三
- 3日/7日/30日留存：将
DATE_ADD(first_login_date, 1)改为 3/7/30，可一次算出多窗口留存率 - 按渠道对比留存质量：关联渠道表，GROUP BY 加入 channel，对比不同获客渠道的用户留存差异
- 留存率趋势图：按天输出留存率，用折线图展示留存率的日度波动
- 回访率（非严格次日）：只判断次日之后是否又登录过（不限次日当天），衡量"是否回来过"
七、知识点总结
| 考点 | 说明 |
|---|---|
| MIN OVER 窗口函数 | 不缩行地获取每个用户的首次登录日期 |
| LEFT JOIN + DATE_ADD | 关联次日登录记录，NULL表示未留存 |
| COUNT DISTINCT 分子分母 | 新增用户数和留存用户数都用DISTINCT去重 |
| ROUND 格式化留存率 | 保留4位小数，0.6667 = 66.67% |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t10_login_log (
user_id string COMMENT '用户ID',
login_date string COMMENT '登录日期'
) COMMENT '用户登录日志表';
-- 数据插入
INSERT INTO t10_login_log VALUES
('u01', '2023-03-01'),
('u02', '2023-03-01'),
('u03', '2023-03-01'),
('u01', '2023-03-02'),
('u02', '2023-03-02'),
('u04', '2023-03-02'),
('u05', '2023-03-02'),
('u01', '2023-03-03'),
('u03', '2023-03-03'),
('u04', '2023-03-03'),
('u05', '2023-03-03'),
('u06', '2023-03-03'),
('u07', '2023-03-03'),
('u01', '2023-03-04'),
('u02', '2023-03-04'),
('u06', '2023-03-04'),
('u07', '2023-03-04');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
