SQL 每个用户最长连续活跃天数：连续问题经典解法（腾讯面试题）

数据仓库晨曦

发布于 2026-07-21 15:49:52

1510

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

这道题来自腾讯微信/QQ的用户活跃度分析岗面试。最长连续活跃天数是衡量用户"上瘾程度"的核心指标——累计活跃天数可以被一次促销活动撑起来，但连续活跃天数反映的是用户是否养成了使用习惯。

业务场景：微信的"连续登录打卡"功能和QQ的"活跃天数勋章"体系背后就是连续活跃天数的计算。产品团队用这个指标找到"可能流失"的用户——昨天还在活跃、今天突然中断了的用户，需要Push拉回。

二、题目

现有一张用户活跃日志表 t11_user_active_log，记录了用户每天的活跃情况（表中每个用户每天最多一条记录）。请计算每个用户的最长连续活跃天数。

用户活跃日志表 t11_user_active_log：

+----------+--------------+
| user_id  | active_date  |
+----------+--------------+
| u01      | 2023-03-01   |
| u01      | 2023-03-02   |
| u01      | 2023-03-03   |
| u01      | 2023-03-05   |
| u01      | 2023-03-06   |
| u01      | 2023-03-07   |
| u01      | 2023-03-08   |
| u01      | 2023-03-10   |
| u02      | 2023-03-01   |
| u02      | 2023-03-02   |
| u02      | 2023-03-04   |
| u02      | 2023-03-05   |
| u02      | 2023-03-06   |
| u03      | 2023-03-03   |
| u03      | 2023-03-04   |
| u03      | 2023-03-05   |
| u03      | 2023-03-06   |
| u03      | 2023-03-07   |
+----------+--------------+

三、思路分析

本题是经典的"最大连续天数"问题，核心使用 ROW_NUMBER 差值法。

核心思路：

按用户分组，按活跃日期排序，生成行号 row_num
使用 date_sub(active_date, row_num) 计算差值 —— 连续的日期减去连续递增的行号，得到的日期是相同的
按用户和差值分组，统计每组的天数，即为一个连续活跃段的天数
取每个用户的最大连续天数

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

1. 按用户分组，按活跃日期排序生成行号

执行SQL

select user_id,
       active_date,
       row_number() over (partition by user_id order by active_date) as rn
from t11_user_active_log

执行结果

+----------+--------------+-----+
| user_id  | active_date  | rn  |
+----------+--------------+-----+
| u01      | 2023-03-01   | 1   |
| u01      | 2023-03-02   | 2   |
| u01      | 2023-03-03   | 3   |
| u01      | 2023-03-05   | 4   |
| u01      | 2023-03-06   | 5   |
| u01      | 2023-03-07   | 6   |
| u01      | 2023-03-08   | 7   |
| u01      | 2023-03-10   | 8   |
| u02      | 2023-03-01   | 1   |
| u02      | 2023-03-02   | 2   |
| u02      | 2023-03-04   | 3   |
| u02      | 2023-03-05   | 4   |
| u02      | 2023-03-06   | 5   |
| u03      | 2023-03-03   | 1   |
| u03      | 2023-03-04   | 2   |
| u03      | 2023-03-05   | 3   |
| u03      | 2023-03-06   | 4   |
| u03      | 2023-03-07   | 5   |
+----------+--------------+-----+
18 rows selected (1.029 seconds)(https://www.dwsql.com)

2. 计算 date_sub 差值，确定连续分组

执行SQL

select user_id,
       active_date,
       rn,
       date_sub(active_date, rn) as group_date
from (
    select user_id,
           active_date,
           row_number() over (partition by user_id order by active_date) as rn
    from t11_user_active_log
) t

执行结果

+----------+--------------+-----+-------------+
| user_id  | active_date  | rn  | group_date  |
+----------+--------------+-----+-------------+
| u01      | 2023-03-01   | 1   | 2023-02-28  |
| u01      | 2023-03-02   | 2   | 2023-02-28  |
| u01      | 2023-03-03   | 3   | 2023-02-28  |
| u01      | 2023-03-05   | 4   | 2023-03-01  |
| u01      | 2023-03-06   | 5   | 2023-03-01  |
| u01      | 2023-03-07   | 6   | 2023-03-01  |
| u01      | 2023-03-08   | 7   | 2023-03-01  |
| u01      | 2023-03-10   | 8   | 2023-03-02  |
| u02      | 2023-03-01   | 1   | 2023-02-28  |
| u02      | 2023-03-02   | 2   | 2023-02-28  |
| u02      | 2023-03-04   | 3   | 2023-03-01  |
| u02      | 2023-03-05   | 4   | 2023-03-01  |
| u02      | 2023-03-06   | 5   | 2023-03-01  |
| u03      | 2023-03-03   | 1   | 2023-03-02  |
| u03      | 2023-03-04   | 2   | 2023-03-02  |
| u03      | 2023-03-05   | 3   | 2023-03-02  |
| u03      | 2023-03-06   | 4   | 2023-03-02  |
| u03      | 2023-03-07   | 5   | 2023-03-02  |
+----------+--------------+-----+-------------+
18 rows selected (0.34 seconds)(https://www.dwsql.com)

观察：u01 的 2023-03-01~2023-03-03 的 group_date都是 "2023-02-28"，说明它们是连续的一段；而 2023-03-05~2023-03-08 的 group_date是 "2023-03-01"，是另一连续段。

3. 按用户和group_date分组，统计每段连续天数，取最大值

执行SQL

select user_id,
       max(consecutive_days) as max_consecutive_days
from (
    select user_id,
           group_date,
           count(1) as consecutive_days
    from (
        select user_id,
               active_date,
               date_sub(active_date, rn) as group_date
        from (
            select user_id,
                   active_date,
                   row_number() over (partitionby user_id orderby active_date) as rn
            from t11_user_active_log
        ) t1
    ) t2
    groupby user_id, group_date
) t3
groupby user_id
orderby user_id

执行结果

+----------+-----------------------+
| user_id  | max_consecutive_days  |
+----------+-----------------------+
| u01      | 4                     |
| u02      | 3                     |
| u03      | 5                     |
+----------+-----------------------+
3 rows selected (0.828 seconds)(https://www.dwsql.com)

结果验证：

u01：最长连续段为 03-05 ~ 03-08，共4天
u02：最长连续段为 03-04 ~ 03-06，共3天
u03：最长连续段为 03-03 ~ 03-07，共5天

五、常见坑点

坑1：日期格式不一致导致 date_sub 失败

date_sub('2023-03-01', 1) 接受 yyyy-MM-dd 格式。如果实际数据是 20230301 或 2023/03/01，需要先用 to_date() 转换，否则结果为 NULL，所有记录分到同一组。

坑2：连续天数相等时取MAX的意义

u01 有两段连续：3天（03-01~03-03）和4天（03-05~03-08），MAX() 取4天。如果需求是"当前连续天数"（最近一段），应该取 ORDER BY group_date DESC LIMIT 1 而非 MAX。

坑3：单次活跃也算连续1天

如果某用户只活跃了1天，差值分组后该段计数为1，MAX(1) = 1。业务上是否需要过滤掉"1天"的用户（可能只是路过），可以加 HAVING MAX(consecutive_days) >= 2。

六、举一反三

当前连续天数：取 group_date 最大的一段（最近一段连续），而非所有段的最大值
连续N天以上筛选：HAVING 中加条件，找"连续7天活跃"的发勋章资格用户
最长连续不活跃天数：反过来计算"断签"天数——相邻两段之间隔了多少天
按周/月维度：用 date_trunc('week', active_date) 聚合后统计"连续活跃的周数"，而非连续天数

七、知识点总结

考点	说明
ROW_NUMBER + date_sub 差值法	连续日期的分组标识：差值相同=同一连续段
GROUP BY user_id, group_date	按用户+分组标识统计每段连续天数
MAX + GROUP BY user_id	取每个用户的最长连续段
三层子查询	行号→差值→分组聚合→取最大值，层层递进

八、建表语句和数据插入

CREATE TABLE t11_user_active_log (
    user_id     stringCOMMENT'用户ID',
    active_date stringCOMMENT'活跃日期'
) COMMENT'用户活跃日志表';

-- 数据插入
INSERTINTO t11_user_active_log VALUES
('u01', '2023-03-01'),
('u01', '2023-03-02'),
('u01', '2023-03-03'),
('u01', '2023-03-05'),
('u01', '2023-03-06'),
('u01', '2023-03-07'),
('u01', '2023-03-08'),
('u01', '2023-03-10'),
('u02', '2023-03-01'),
('u02', '2023-03-02'),
('u02', '2023-03-04'),
('u02', '2023-03-05'),
('u02', '2023-03-06'),
('u03', '2023-03-03'),
('u03', '2023-03-04'),
('u03', '2023-03-05'),
('u03', '2023-03-06'),
('u03', '2023-03-07');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-20，如有侵权请联系 cloudcommunity@tencent.com 删除

腾讯