B站大数据面试SQL-番剧追番用户留存分析

⚠️ 待修正

一、题目背景

这道题来自B站的数据分析岗面试。B站是弹幕视频社区，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：用户粘性相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

现有一张追番观看表 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获取每个用户下一周是否观看，计算用户留存率 = 下周继续观看人数 / 本周观看人数。

四、逐步推导

select anime_id, watch_week,
       round(sum(case when next_week is not null then 1 else 0 end) / count(1), 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
    from t5_anime_watch
) t
group by anime_id, watch_week

执行结果

+----------+-------------+----------------+
| anime_id | watch_week  | retention_rate |
+----------+-------------+----------------+
| AN001    | 2023W01     | 0.5000         |
| AN001    | 2023W02     | 1.0000         |
+----------+-------------+----------------+

W01→W02留存率50%（仅u01继续），W02→W03留存率100%。

五、常见坑点

坑1：最近日期的新用户无法计算留存 — 最后一天新增用户尚未有"次日"，留存率=0/NULL，实际应用需排除最近N天。

坑2：LEFT JOIN条件不完整导致虚高 — 必须同时匹配 user_id 和 date，只匹配 user_id 会把隔天也算成次日留存。

六、举一反三

1. 3日/7日/30日留存：DATE_ADD偏移量从1改为3/7/30，一次算出多窗口留存

2. 按渠道计算留存质量：GROUP BY加channel字段，对比不同获客渠道的用户留存

3. 留存曲线：对每个首次登录日在后续1-30天分别LEFT JOIN，画出完整留存衰减趋势

七、知识点总结

考点	说明
LAG / LEAD	获取前/后一行数据，用于环比计算、状态变更检测
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t5_anime_watch (user_id string, anime_id string, watch_week string);
INSERT INTO t5_anime_watch VALUES
('u01','AN001','2023W01'),('u01','AN001','2023W02'),('u01','AN001','2023W03'),
('u02','AN001','2023W01'),('u02','AN001','2023W03');

📱关注公众号

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

💬加群交流

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