跳到主要内容

快手大数据面试SQL-视频播放进度分析（完播率统计）

⚠️ 待修正

一、题目背景​

这道题来自快手数据平台部的数据分析岗面试。快手作为短视频平台，完播率是衡量视频内容质量的核心指标——用户是否完整观看了视频，直接决定了算法推荐权重。完播率高的视频会获得更多流量倾斜，因此精准统计完播率对内容分发策略至关重要。

业务场景：推荐算法团队需要根据完播率筛选优质视频进入更大的流量池。同时运营团队也需要按完播率评估不同类型内容的吸引力，指导创作者的内容方向调整。每个用户对同一视频的重复播放只取最长播放记录，避免因用户反复观看同一视频导致数据失真。

二、题目​

已知有表t1_video_play_log记录了用户视频播放行为，包含字段：video_id（视频ID）、user_id（用户ID）、video_duration（视频总时长，单位秒）、play_duration（用户实际播放时长，单位秒）、play_date（播放日期）。

请统计每个视频的完播率以及整体完播率。完播的定义为：用户实际播放时长 >= 视频总时长 * 0.9（即播放超过90%视为完播）。每个用户对同一个视频只计算一次（取该用户最长播放记录）。

样例数据

+----------+---------+----------------+---------------+------------+
| video_id | user_id | video_duration | play_duration | play_date  |
+----------+---------+----------------+---------------+------------+
| V001     | u001    | 60             | 60            | 2024-06-01 |
| V001     | u002    | 60             | 45            | 2024-06-01 |
| V001     | u003    | 60             | 55            | 2024-06-01 |
| V001     | u003    | 60             | 58            | 2024-06-02 |
| V001     | u004    | 60             | 10            | 2024-06-01 |
| V002     | u001    | 120            | 120           | 2024-06-01 |
| V002     | u002    | 120            | 30            | 2024-06-01 |
| V002     | u005    | 120            | 115           | 2024-06-01 |
| V003     | u001    | 30             | 28            | 2024-06-01 |
| V003     | u004    | 30             | 5             | 2024-06-01 |
+----------+---------+----------------+---------------+------------+

三、思路分析​

本题是短视频平台中非常核心的播放质量指标——完播率。完播率高的视频通常会被算法优先推荐。

核心步骤：

  • 用户去重：每个用户对同一个视频可能有多次播放记录，需要按(video_id, user_id)分组，取该用户对该视频的最长播放时长。使用ROW_NUMBER可以完成这个操作。
  • 完播判断：对去重后的数据，判断play_duration >= video_duration * 0.9。这里用CASE WHEN或直接布尔判断。
  • 完播率计算：按video_id分组，完播记录数 / 总记录数。整体完播率可以通过去掉GROUP BY直接计算。

在实际业务中，完播率还可以按视频时长区间（如 <15s、15-60s、>60s）、按视频类型等维度进一步细分。

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

四、逐步推导​

步骤1：用户去重——每个用户对每个视频取最长播放记录​

使用ROW_NUMBER按(video_id, user_id)分组，按play_duration降序排序，取rn=1即为每个用户对每个视频的最长播放记录。

执行SQL

select
    video_id,
    user_id,
    video_duration,
    play_duration,
    play_date
from (
    select
        video_id,
        user_id,
        video_duration,
        play_duration,
        play_date,
        row_number() over (partition by video_id, user_id order by play_duration desc) as rn
    from t1_video_play_log
) t
where rn = 1

执行结果

+----------+---------+----------------+---------------+------------+
| video_id | user_id | video_duration | play_duration | play_date  |
+----------+---------+----------------+---------------+------------+
| V001     | u001    | 60             | 60            | 2024-06-01 |
| V001     | u002    | 60             | 45            | 2024-06-01 |
| V001     | u003    | 60             | 58            | 2024-06-02 |
| V001     | u004    | 60             | 10            | 2024-06-01 |
| V002     | u001    | 120            | 120           | 2024-06-01 |
| V002     | u002    | 120            | 30            | 2024-06-01 |
| V002     | u005    | 120            | 115           | 2024-06-01 |
| V003     | u001    | 30             | 28            | 2024-06-01 |
| V003     | u004    | 30             | 5             | 2024-06-01 |
+----------+---------+----------------+---------------+------------+

可以看到u003对V001有两条记录（55s和58s），去重后仅保留了最长播放的58s记录。

步骤2：统计每个视频的完播率​

按video_id分组，使用条件聚合计算完播用户数和总用户数，完播率 = 完播用户数 / 总用户数。

执行SQL

with user_video as (
    select
        video_id,
        user_id,
        video_duration,
        play_duration
    from (
        select
            video_id,
            user_id,
            video_duration,
            play_duration,
            row_number() over (partition by video_id, user_id order by play_duration desc) as rn
        from t1_video_play_log
    ) t
    where rn = 1
)
select
    video_id,
    count(distinct user_id) as total_users,
    sum(case when play_duration >= video_duration * 0.9 then 1 else 0 end) as completed_users,
    round(
        sum(case when play_duration >= video_duration * 0.9 then 1 else 0 end) * 100.0
        / count(distinct user_id),
        2
    ) as completion_rate
from user_video
group by video_id

执行结果

+----------+-------------+-----------------+-----------------+
| video_id | total_users | completed_users | completion_rate |
+----------+-------------+-----------------+-----------------+
| V001     | 4           | 1               | 25.00           |
| V002     | 3           | 2               | 66.67           |
| V003     | 2           | 1               | 50.00           |
+----------+-------------+-----------------+-----------------+

步骤3：计算平台整体完播率​

不按video_id分组，直接计算所有视频的整体完播率。

执行SQL

with user_video as (
    select
        video_id,
        user_id,
        video_duration,
        play_duration
    from (
        select
            video_id,
            user_id,
            video_duration,
            play_duration,
            row_number() over (partition by video_id, user_id order by play_duration desc) as rn
        from t1_video_play_log
    ) t
    where rn = 1
)
select
    count(distinct user_id) as total_playbacks,
    sum(case when play_duration >= video_duration * 0.9 then 1 else 0 end) as completed_playbacks,
    round(
        sum(case when play_duration >= video_duration * 0.9 then 1 else 0 end) * 100.0
        / count(distinct user_id),
        2
    ) as overall_completion_rate
from user_video

执行结果

+-----------------+----------------------+---------------------------+
| total_playbacks | completed_playbacks  | overall_completion_rate   |
+-----------------+----------------------+---------------------------+
| 9               | 4                    | 44.44                     |
+-----------------+----------------------+---------------------------+

五、常见坑点​

坑1：忘记按用户去重，导致重复播放放大数据

同一个用户可能多次播放同一视频（如反复观看精彩片段）。如果不先去重，该用户的多次播放会被多次计入分母和分子，导致完播率失真。正确做法是先用ROW_NUMBER取每个用户对每个视频的最长播放记录，再去计算指标。

坑2：完播阈值设错导致结果偏差

完播的阈值通常不是100%而是90%或95%。因为用户可能在最后1-2秒滑动到下一个视频，如果严格用100%判断，完播率会极低，失去参考价值。本题明确要求90%，但如果面试中没有说明，需要主动向面试官确认阈值口径。

坑3：COUNT(DISTINCT user_id) 在整体完播率中的歧义

计算整体完播率时，一个用户可能播放了多个视频。如果直接用 COUNT(DISTINCT user_id) 会低估总数的实际含义——分母应该是"去重后的(视频,用户)对"的数量，而不是"去重用户数"。示例数据中因为没有跨视频的重复用户所以碰巧一致，但实际业务中需要注意这个区别。

六、举一反三​

  1. 按时长区间分档统计完播率：将视频按总时长分为 <15s（短视频）、15-60s（中视频）、>60s（长视频）三档，在GROUP BY中加入时长区间维度，分析不同时长视频的完播表现差异，指导内容创作方向。

  2. 完播率+互动率复合指标：在完播的基础上，还可关联评论/点赞/分享表，计算"完播且互动"的比率，这是比单纯完播率更强的质量信号，常用于精选推荐池的准入标准。

  3. 计算用户的完播偏好：按user_id分组统计每个用户的平均完播率，用于用户画像标签建设。完播率高的用户对内容更挑剔，可以给他们推更优质的内容；完播率低的用户可能是"滑动党"，适合推短平快的内容。

  4. 完播率的每日趋势监控：按play_date分组统计每天的完播率变化趋势，可配合A/B测试或算法改动做效果评估——完播率的涨跌直接反映推荐策略的优劣。

七、知识点总结​

考点说明
ROW_NUMBER() 去重按(video_id, user_id)分区取最长播放记录
CASE WHEN 条件聚合判断 play_duration >= video_duration * 0.9 为完播
WITH CTE 复用子查询将去重结果封装为CTE，避免重复编写子查询
比率计算 + ROUND用条件聚合结果除以COUNT得到百分比，注意乘以100.0避免整数除法

八、建表语句和数据插入​

点击展开 DDL & DML
--建表语句
CREATE TABLE IF NOT EXISTS t1_video_play_log (
    video_id STRING COMMENT '视频ID',
    user_id STRING COMMENT '用户ID',
    video_duration BIGINT COMMENT '视频总时长(秒)',
    play_duration BIGINT COMMENT '用户实际播放时长(秒)',
    play_date STRING COMMENT '播放日期'
);

--数据插入
INSERT INTO t1_video_play_log(video_id, user_id, video_duration, play_duration, play_date) VALUES
('V001', 'u001', 60, 60, '2024-06-01'),
('V001', 'u002', 60, 45, '2024-06-01'),
('V001', 'u003', 60, 55, '2024-06-01'),
('V001', 'u003', 60, 58, '2024-06-02'),
('V001', 'u004', 60, 10, '2024-06-01'),
('V002', 'u001', 120, 120, '2024-06-01'),
('V002', 'u002', 120, 30, '2024-06-01'),
('V002', 'u005', 120, 115, '2024-06-01'),
('V003', 'u001', 30, 28, '2024-06-01'),
('V003', 'u004', 30, 5, '2024-06-01');
📱关注公众号

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

微信公众号二维码
💬加群交流

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

交流微信二维码

你可能还想看

相关题目
04. 直播间礼物价值Top N观众06. 视频发布后24小时内的播放量01. 最高峰同时直播人数