跳到主要内容

SQL 新用户次日留存率：MIN OVER + CASE WHEN 计算留存（腾讯面试题）

一、题目背景​

这道题来自腾讯微信/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  |
+----------+-------------+

三、思路分析​

本题是数据分析中非常经典的"留存率"计算问题，核心思路如下：

  1. 确定每个用户的首次登录日期（即新增日期）：使用 MIN() OVER() 窗口函数保留全部登录明细，同时为每行打上 first_login_date 标签；
  2. 用 CASE WHEN 判断次日是否登录：由于 MIN() OVER() 保留了所有行，直接在子查询结果上用 CASE WHEN login_date = DATE_ADD(first_login_date, 1) 识别次日留存的用户，无需再 JOIN 原表；
  3. 按新增日期分组计算留存率：COUNT(DISTINCT CASE WHEN ...) 统计次日留存用户数 / COUNT(DISTINCT user_id) 新增用户总数；
维度评分
题目难度⭐️⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️⭐️

四、逐步推导​

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-03        | 2             |
| 2023-03-02        | 2             |
+-------------------+---------------+
3 rows selected (0.546 seconds)(https://www.dwsql.com)

3. 计算次日留存用户数及留存率​

步骤1的结果已经同时保留了每个用户的 login_date 和 first_login_date，无需再 JOIN 原表。直接用 CASE WHEN 判断 login_date = DATE_ADD(first_login_date, 1) 即可识别次日留存的用户。

执行SQL

select first_login_date,
       count(distinct user_id) as new_user_cnt,
       count(distinct case when login_date = date_add(first_login_date, 1)
                           then user_id end) as retain_user_cnt,
       round(count(distinct case when login_date = date_add(first_login_date, 1)
                                 then user_id end) / count(distinct user_id), 4) as retain_rate
from (
    select user_id,
           login_date,
           min(login_date) over (partition by user_id) as first_login_date
    from t10_login_log
) t
group by first_login_date
order by 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.0          |
| 2023-03-03        | 2             | 2                | 1.0          |
+-------------------+---------------+------------------+--------------+
3 rows selected (10.217 seconds)(https://www.dwsql.com)

结果解读：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：冗余 LEFT JOIN 回原表

常见写法是在计算留存率时再次 LEFT JOIN 登录日志表（按 user_id + login_date = first_login_date + 1），但 MIN() OVER() 已经保留了每行的 login_date，直接用 CASE WHEN 判断即可，无需额外的 JOIN。减少一次 JOIN 意味着少扫描一次大表，在大数据量下性能差异明显。

六、举一反三​

  1. 3日/7日/30日留存：将 DATE_ADD(first_login_date, 1) 改为 3/7/30，可一次算出多窗口留存率
  2. 按渠道对比留存质量：关联渠道表，GROUP BY 加入 channel，对比不同获客渠道的用户留存差异
  3. 留存率趋势图：按天输出留存率，用折线图展示留存率的日度波动
  4. 回访率（非严格次日）：只判断次日之后是否又登录过（不限次日当天），衡量"是否回来过"

七、知识点总结​

考点说明
MIN OVER 窗口函数不缩行地获取每个用户的首次登录日期，保留全部登录明细
CASE WHEN + COUNT DISTINCT在子查询结果上直接判断次日是否登录，避免冗余 LEFT JOIN 回原表
留存率公式COUNT(DISTINCT 留存用户) / COUNT(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真题

交流微信二维码

你可能还想看

专题
窗口函数专题
相关题目
05. 连续登录超过N天的用户01. 向用户推荐好友喜欢的音乐02. 占据好友封面个数