跳到主要内容

字节跳动大数据面试SQL-最大连续登录天数

一、题目背景​

这道题是 SQL 面试中"连续问题"的终极形态，字节、阿里、腾讯的面试中反复出现。它的核心技巧——日期减 ROW_NUMBER 差值法——可以用来解决所有"连续XXX"类问题。

业务场景：用户运营需要找出"高活跃用户"（连续登录天数最长的用户），给他们推送 VIP 权益。同时也要找出"即将流失的用户"（连续登录天数突然下降）。

二、题目​

现有一张用户登录日志表 t17_zj_user_login，记录了用户每天的登录情况（每天每用户至多一条记录）。请找出每个用户的最大连续登录天数。

t17_zj_user_login 表

+----------+-------------+
| user_id  | login_date  |
+----------+-------------+
| 1        | 2025-01-01  |
| 1        | 2025-01-02  |
| 1        | 2025-01-03  |
| 1        | 2025-01-05  |
| 1        | 2025-01-06  |
| 1        | 2025-01-07  |
| 2        | 2025-01-01  |
| 2        | 2025-01-02  |
| 2        | 2025-01-04  |
| 2        | 2025-01-05  |
| 3        | 2025-01-03  |
| 3        | 2025-01-04  |
+----------+-------------+

期望输出：每个用户的最大连续登录天数。

三、思路分析​

核心是日期 - ROW_NUMBER 差值法：

  1. 按 user_id 分区，按 login_date 排序，生成 ROW_NUMBER
  2. 计算 login_date - ROW_NUMBER，连续的日期会得到相同的差值
  3. 按差值分组，COUNT 求每组连续天数，MAX 取最大值

原理图解：

用户1 的日期:
01-01 → ROW_NUMBER=1 → 差值=01-01 - 1 = 12-31
01-02 → ROW_NUMBER=2 → 差值=01-02 - 2 = 12-31  ← 相同的差值！
01-03 → ROW_NUMBER=3 → 差值=01-03 - 3 = 12-31  ← 相同的差值！
01-05 → ROW_NUMBER=4 → 差值=01-05 - 4 = 01-01  ← 断了一天，差值变了
01-06 → ROW_NUMBER=5 → 差值=01-06 - 5 = 01-01
01-07 → ROW_NUMBER=6 → 差值=01-07 - 6 = 01-01

分组1 (差值12-31): 3天  |  分组2 (差值01-01): 3天  → 最大3天
维度评分
题目难度⭐️⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️⭐️

四、逐步推导​

步骤1：生成 ROW_NUMBER​

Spark SQL

SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM t17_zj_user_login;

执行结果

+----------+-------------+-----+
| user_id  | login_date  | rn  |
+----------+-------------+-----+
| 1        | 2025-01-01  | 1   |
| 1        | 2025-01-02  | 2   |
| 1        | 2025-01-03  | 3   |
| 1        | 2025-01-05  | 4   |
| 1        | 2025-01-06  | 5   |
| 1        | 2025-01-07  | 6   |
| 2        | 2025-01-01  | 1   |
| 2        | 2025-01-02  | 2   |
| 2        | 2025-01-04  | 3   |
| 2        | 2025-01-05  | 4   |
| 3        | 2025-01-03  | 1   |
| 3        | 2025-01-04  | 2   |
+----------+-------------+-----+
12 rows selected (8.494 seconds)(https://www.dwsql.com)

步骤2：日期减 ROW_NUMBER​

这次咱们直接使用减完的日期作为分组字段，之前都是使用差值作为分组字段，其实差值用什么都可以，序号、差值、连续的开始日期等等，只要保证用户内不重复就可以。

Spark SQL

SELECT
    user_id,
    login_date,
    DATE_SUB(login_date, rn) AS grp
FROM (
    SELECT user_id, login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM t17_zj_user_login
) t;

执行结果

+----------+-------------+-------------+
| user_id  | login_date  |     grp     |
+----------+-------------+-------------+
| 1        | 2025-01-01  | 2024-12-31  |
| 1        | 2025-01-02  | 2024-12-31  |
| 1        | 2025-01-03  | 2024-12-31  |
| 1        | 2025-01-05  | 2025-01-01  |
| 1        | 2025-01-06  | 2025-01-01  |
| 1        | 2025-01-07  | 2025-01-01  |
| 2        | 2025-01-01  | 2024-12-31  |
| 2        | 2025-01-02  | 2024-12-31  |
| 2        | 2025-01-04  | 2025-01-01  |
| 2        | 2025-01-05  | 2025-01-01  |
| 3        | 2025-01-03  | 2025-01-02  |
| 3        | 2025-01-04  | 2025-01-02  |
+----------+-------------+-------------+
12 rows selected (0.409 seconds)(https://www.dwsql.com)

步骤3：按差值分组，求最长连续天数​

Spark SQL

SELECT
    user_id,
    MAX(consecutive_days) AS max_consecutive_days
FROM (
    SELECT
        user_id,
        grp,
        COUNT(*) AS consecutive_days
    FROM (
        SELECT
            user_id,
            login_date,
            DATE_SUB(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp
        FROM t17_zj_user_login
    ) t
    GROUP BY user_id, grp
) t2
GROUP BY user_id
ORDER BY user_id;

最终结果：

+----------+-----------------------+
| user_id  | max_consecutive_days  |
+----------+-----------------------+
| 1        | 3                     |
| 2        | 2                     |
| 3        | 2                     |
+----------+-----------------------+
3 rows selected (0.805 seconds)(https://www.dwsql.com)

五、常见坑点​

坑1：DATE_SUB 的参数顺序

DATE_SUB(date, days) —— 第一个是日期，第二个是天数。如果用错成 DATE_SUB(rn, login_date) 会得到错误结果或者报错。

坑2：Spark SQL 的 DATE_SUB

Spark SQL 中 DATE_SUB(login_date, rn) 正确处理整数 rn。但在 Hive 中可能需要 DATE_SUB(login_date, CAST(rn AS INT))。

坑3：同一天多条登录记录

本例假设每天每用户只有一条记录。如果有重复，需要先 SELECT DISTINCT user_id, login_date 去重，否则 ROW_NUMBER 会编号到同一天的重复记录，差值计算会出错。

六、举一反三​

  1. 允许间隔1天：把 DATEDIFF = 1 改为 DATEDIFF <= 2，允许中间断1天也算连续
  2. 连续活跃N周：把日期先 trunc 到周 DATE_TRUNC('week', login_date)，再套用差值法
  3. 当前连续天数：不只是历史最大，还要知道"截止今天正在连续的最近一段有多少天"

七、知识点总结​

考点说明
ROW_NUMBER + PARTITION BY按用户分区编号
日期 - ROW_NUMBERGaps and Islands 差值法核心
相同差值 = 连续连续日期的数学特性
DATE_SUB日期减去整数天数
MAX + GROUP BY取得每个用户的最长连续段

八、建表语句和数据插入​

点击展开 DDL & DML
CREATE TABLE IF NOT EXISTS t17_zj_user_login (
    user_id INT,
    login_date STRING
);

INSERT INTO t17_zj_user_login VALUES
(1, '2025-01-01'),(1, '2025-01-02'),(1, '2025-01-03'),
(1, '2025-01-05'),(1, '2025-01-06'),(1, '2025-01-07'),
(2, '2025-01-01'),(2, '2025-01-02'),(2, '2025-01-04'),(2, '2025-01-05'),
(3, '2025-01-03'),(3, '2025-01-04');
📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

微信公众号二维码
💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

交流微信二维码

你可能还想看

专题
窗口函数专题连续问题专题
相关题目
08. 寻找电影院连续3个空闲座位14. 连续3天交易金额递增的用户05. 查询最近一笔有效订单