腾讯大数据面试SQL-每个用户最长连续活跃天数
⚠️ 待修正
一、题目背景
这道题来自腾讯微信/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 |
+----------+--------------+-----+
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 |
+----------+--------------+-----+-------------+
观察：u01 的 2023-03-01
2023-03-03 的2023-03-08 的group_date都是 "2023-02-28"，说明它们是连续的一段；而 2023-03-05group_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 (partition by user_id order by active_date) as rn
from t11_user_active_log
) t1
) t2
group by user_id, group_date
) t3
group by user_id
order by user_id
执行结果
+----------+----------------------+
| user_id | max_consecutive_days |
+----------+----------------------+
| u01 | 4 |
| u02 | 3 |
| u03 | 5 |
+----------+----------------------+
结果验证：
- 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-0103-03）和4天（03-0503-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 | 取每个用户的最长连续段 |
| 三层子查询 | 行号→差值→分组聚合→取最大值，层层递进 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t11_user_active_log (
user_id string COMMENT '用户ID',
active_date string COMMENT '活跃日期'
) COMMENT '用户活跃日志表';
-- 数据插入
INSERT INTO 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');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
