字节跳动大数据面试SQL-视频完播率统计
一、题目背景
这道题来自字节跳动短视频业务的数据分析岗面试。对于抖音这样的产品，完播率是衡量内容质量的核心指标——用户有没有把这个视频看完？完播率高的视频会被推荐算法加权分发，获得更多曝光。
业务场景：运营需要一份"视频质量报表"，按视频维度统计完播率，完播率低的视频需要人工复审（是不是封面党？开头太拖沓？），完播率高的视频则进入精品池。
二、题目
现有一张用户播放行为表 t10_zj_video_play，记录了用户每次播放视频的时长和视频总时长。请统计每个视频的完播率。
t10_zj_video_play 表
+----------+-----------+----------------+---------------+
| user_id | video_id | play_duration | video_length |
+----------+-----------+----------------+---------------+
| 1 | V001 | 45 | 60 |
| 2 | V001 | 60 | 60 |
| 3 | V001 | 30 | 60 |
| 1 | V002 | 15 | 15 |
| 2 | V002 | 10 | 15 |
| 3 | V002 | 15 | 15 |
| 1 | V003 | 90 | 120 |
| 2 | V003 | 120 | 120 |
| 3 | V003 | 60 | 120 |
| 4 | V003 | 5 | 120 |
+----------+-----------+----------------+---------------+
定义：完播 = 播放时长 >= 视频时长。完播率 = 完播次数 / 总播放次数。
期望输出：每个视频的播放次数、完播次数、完播率(%)。
三、思路分析
核心是条件聚合：SUM(CASE WHEN 完播条件 THEN 1 ELSE 0 END)。
- 判断每行是否完播：
play_duration >= video_length - 按 video_id 分组，SUM 统计完播次数，COUNT 统计总次数
- 完播率 = 完播次数 / 总播放次数 × 100
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：逐行判断是否完播
SELECT
user_id,
video_id,
play_duration,
video_length,
CASE WHEN play_duration >= video_length THEN 1 ELSE 0 END AS is_complete
FROM t10_zj_video_play;
执行结果：
+----------+-----------+----------------+---------------+--------------+
| user_id | video_id | play_duration | video_length | is_complete |
+----------+-----------+----------------+---------------+--------------+
| 1 | V001 | 45 | 60 | 0 |
| 2 | V001 | 60 | 60 | 1 |
| 3 | V001 | 30 | 60 | 0 |
| 1 | V002 | 15 | 15 | 1 |
| 2 | V002 | 10 | 15 | 0 |
| 3 | V002 | 15 | 15 | 1 |
| 1 | V003 | 90 | 120 | 0 |
| 2 | V003 | 120 | 120 | 1 |
| 3 | V003 | 60 | 120 | 0 |
| 4 | V003 | 5 | 120 | 0 |
+----------+-----------+----------------+---------------+--------------+
10 rows selected (0.369 seconds)(dwsql.com)
步骤2：按视频聚合统计
SELECT
video_id,
COUNT(*) AS total_plays,
SUM(CASE WHEN play_duration >= video_length THEN 1 ELSE 0 END) AS complete_plays,
ROUND(
SUM(CASE WHEN play_duration >= video_length THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
1
) AS completion_rate
FROM t10_zj_video_play
GROUP BY video_id
ORDER BY video_id;
最终结果：
+-----------+--------------+-----------------+------------------+
| video_id | total_plays | complete_plays | completion_rate |
+-----------+--------------+-----------------+------------------+
| V001 | 3 | 1 | 33.3 |
| V002 | 3 | 2 | 66.7 |
| V003 | 4 | 1 | 25.0 |
+-----------+--------------+-----------------+------------------+
3 rows selected (1.234 seconds)(dwsql.com)
分析：V002 是15秒短视频，完播率最高 66.7%。V003 是120秒长视频，完播率仅 25%——可能需要优化开头3秒的"黄金前奏"。
五、常见坑点
坑1：播放时长超过视频时长
用户可能拖拽进度条重复观看，导致 play_duration > video_length。这仍然算完播——条件用 >= 而非 = 即可覆盖。
坑2：video_length 为 0
如果视频时长为 0（数据异常），play_duration >= 0 恒为 True，所有播放都算完播。应提前过滤或标记异常。
六、举一反三
- 按用户维度：统计每个用户的完播率，用于用户画像——"高质量内容消费者"标签
- 分段完播率：不是只看 >= 100%，而是分桶 (0-25%, 25-50%, 50-75%, 75-100%)，画出播放完成度分布
- 加权完播率：如果用户是"点击后5秒内关闭"，权重比"看了80%后关闭"更低
七、知识点总结
| 考点 | 说明 |
|---|---|
| CASE WHEN 条件聚合 | SUM(CASE WHEN cond THEN 1 ELSE 0 END) 条件计数 |
* 100.0 | 触发浮点除法，避免整数截断 |
| 完播率公式 | 完播次数 / 总播放次数 × 100% |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE IF NOT EXISTS t10_zj_video_play (
user_id INT,
video_id STRING,
play_duration INT,
video_length INT
);
INSERT INTO t10_zj_video_play VALUES
(1, 'V001', 45, 60),
(2, 'V001', 60, 60),
(3, 'V001', 30, 60),
(1, 'V002', 15, 15),
(2, 'V002', 10, 15),
(3, 'V002', 15, 15),
(1, 'V003', 90, 120),
(2, 'V003', 120, 120),
(3, 'V003', 60, 120),
(4, 'V003', 5, 120);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
