SQL 番剧追番用户留存：次周留存率计算（B站面试题）

一、题目背景

这道题来自B站番剧业务部门的数据分析岗面试。B站是国内最大的番剧正版播放平台，番剧是典型的"周更追番"模式——用户每周追看最新一集。番剧运营团队需要关注每部番剧的追番用户留存率：如果某部番剧从第3集开始大量用户流失，说明剧情或制作出现问题，运营可以调整推广策略。

业务场景：番剧季中复盘时，需要输出"各部番剧的周留存率趋势"，识别"高开低走"（首集热度高但留存差）的番剧，与版权采购团队共享数据辅助后续选片决策。

二、题目

现有一张追番观看表 t5_anime_watch，记录用户每周观看番剧的情况。统计每部番剧每周的次周留存率。

表 t5_anime_watch：

+----------+-----------+-------------+
| user_id  | anime_id  | watch_week  |
+----------+-----------+-------------+
| u01      | AN001     | 2023W01     |
| u01      | AN001     | 2023W02     |
| u01      | AN001     | 2023W03     |
| u02      | AN001     | 2023W01     |
| u02      | AN001     | 2023W03     |
+----------+-----------+-------------+

三、思路分析

使用LEAD获取每个用户下一周是否观看，计算用户留存率 = 下周继续观看人数 / 本周观看人数。

四、逐步推导

步骤1：用LEAD获取每个用户下周的观看记录

select user_id, anime_id, watch_week,
       lead(watch_week) over (partition by user_id, anime_id order by watch_week) as next_watch_week
from t5_anime_watch

执行结果

+----------+-----------+-------------+------------------+
| user_id  | anime_id  | watch_week  | next_watch_week  |
+----------+-----------+-------------+------------------+
| u01      | AN001     | 2023W01     | 2023W02          |
| u01      | AN001     | 2023W02     | 2023W03          |
| u01      | AN001     | 2023W03     | NULL             |
| u02      | AN001     | 2023W01     | 2023W03          |
| u02      | AN001     | 2023W03     | NULL             |
+----------+-----------+-------------+------------------+
5 rows selected (0.819 seconds)(https://www.dwsql.com)

u02在W01后跳到了W03（中间跳过了W02），单纯的 next_week is not null 不能判定"连续观看"，需要判断next_week是否恰好是下一周。

步骤2：验证是否为次周连续观看，按周统计留存率

select anime_id, watch_week,
       count(distinct user_id) as watch_cnt,
       count(distinct case when next_week = expected_next then user_id end) as retain_cnt,
       round(count(distinct case when next_week = expected_next then user_id end) / count(distinct user_id), 4) as retention_rate
from (
    select user_id, anime_id, watch_week,
           lead(watch_week) over (partition by user_id, anime_id order by watch_week) as next_week,
           -- 计算下一周的周标识（如2023W01 → 2023W02）
           concat(substr(watch_week,1,4), 'W', lpad(cast(substr(watch_week,6) as int)+1, 2, '0')) as expected_next
    from t5_anime_watch
) t
group by anime_id, watch_week

执行结果

+-----------+-------------+------------+-------------+-----------------+
| anime_id  | watch_week  | watch_cnt  | retain_cnt  | retention_rate  |
+-----------+-------------+------------+-------------+-----------------+
| AN001     | 2023W01     | 2          | 1           | 0.5             |
| AN001     | 2023W02     | 1          | 1           | 1.0             |
| AN001     | 2023W03     | 2          | 0           | 0.0             |
+-----------+-------------+------------+-------------+-----------------+
3 rows selected (1.171 seconds)(https://www.dwsql.com)

W01→W02留存率50%（仅u01连续观看，u02跳过W02不算留存）。W03为最后一周，无可计算的下一周。

五、常见坑点

坑1：仅判断 NOT NULL 忽略了跳跃式观看

用户可能跳过几周后再观看（如u02从W01直接跳到W03），仅判断 LEAD 结果是否为空会把跨周也算作"留存"，导致留存率虚高。必须验证 next_week 是否恰好等于下一周的周标识。

坑2：字符串格式的周标识难以做算术运算

2023W01 是字符串，不能直接用 +1 计算下一周。需要拆解为年份+周数分别处理，同时考虑跨年（2023W52→2024W01）的边界情况。面试中可以先说明"此处简化处理，实际项目建议用日期字段配合 DATE_ADD"。

坑3：最后一周的留存率恒为0

最后一周没有"下一周"的数据，LEAD返回NULL，留存率必然为0。这会导致整体留存率被拉低。实际分析时应标注"最新周数据不完整"或直接排除。

六、举一反三

1. 跨周界定留存：如果连续两周都有观看就算留存（不要求必须是相邻周），则可简化为 next_week is not null。业务上需明确是"连续追番"还是"仍在追番"
2. 番剧完结后的长尾留存：对已完结番剧，计算完结后1个月/3个月仍有用户观看的比例，评估番剧的长尾价值
3. 用户追番数 vs 留存率：统计每个用户同时追番的数量，分析"追番≥5部"的用户 vs "追番1-2部"的用户的留存率差异，指导追番提醒策略

七、知识点总结

考点	说明
LEAD + PARTITION BY	获取同一用户同一番剧的下一次观看周，核心用于前后对比
DISTINCT + CASE WHEN	条件去重统计留存用户数，避免同一用户多条记录重复计数
字符串拆解 + 计算	SUBSTR + LPAD 构造"期望下周"标识，用于精确的连续周判定
周留存 vs 日留存	周粒度数据用周标识比较，日粒度用 DATE_ADD，原理相同、实现不同

八、建表语句和数据插入

点击展开 DDL & DML

create table t5_anime_watch (
    user_id    string comment '用户ID',
    anime_id   string comment '番剧ID',
    watch_week string comment '观看周标识（格式YYYYWww）'
) comment '追番观看记录表';

insert into t5_anime_watch values
('u01','AN001','2023W01'),('u01','AN001','2023W02'),('u01','AN001','2023W03'),
('u02','AN001','2023W01'),('u02','AN001','2023W03');

📱关注公众号

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

💬加群交流

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