快手大数据面试SQL-视频发布后24小时内的播放量

一、题目

已知有两张表：

t1_video_publish：视频发布表，记录每个视频的发布信息（video_id, author_id, publish_time）
t1_video_play：视频播放日志表，记录每次播放事件（video_id, user_id, play_time）

请统计每个视频发布后24小时内的播放量（即播放时间在 [publish_time, publish_time + 24小时] 区间内的播放次数），按播放量降序排列。

样例数据

t1_video_publish：

+----------+-----------+----------------------+
| video_id | author_id |     publish_time     |
+----------+-----------+----------------------+
| V001     | a001      | 2024-06-01 08:00:00  |
| V002     | a002      | 2024-06-01 10:00:00  |
| V003     | a001      | 2024-06-02 12:00:00  |
+----------+-----------+----------------------+

t1_video_play：

+----------+---------+----------------------+
| video_id | user_id |      play_time       |
+----------+---------+----------------------+
| V001     | u001    | 2024-06-01 08:30:00  |
| V001     | u002    | 2024-06-01 09:00:00  |
| V001     | u003    | 2024-06-02 07:00:00  |
| V001     | u004    | 2024-06-02 10:00:00  |
| V002     | u001    | 2024-06-01 12:00:00  |
| V002     | u002    | 2024-06-02 08:00:00  |
| V003     | u003    | 2024-06-02 13:00:00  |
| V003     | u005    | 2024-06-02 20:00:00  |
| V003     | u001    | 2024-06-03 13:00:00  |
+----------+---------+----------------------+

三、思路分析

本题考察的是时间窗口内数据关联统计，属于典型的短视频数据分析场景。核心要点：

JOIN + 时间过滤：将视频发布表与播放日志表按 video_id 关联，然后通过 WHERE 条件过滤掉播放时间超出 [发布时间, 发布时间+24小时] 的记录。
时间范围计算：不同SQL引擎的时间加减写法不同。在通用SQL中，可以使用 date_add(publish_time, interval 1 day) 来表示发布时间+24小时。在Spark SQL中也可用 publish_time + interval 24 hours 或 unix_timestamp 转换后计算。
注意边界：题目要求"发布后24小时内"，即 play_time >= publish_time AND play_time < publish_time + 24 hours（或 <=）。通常左闭右开即可，具体看业务口径。

视频发布后的24小时播放量（又称首日播放量）是快手衡量视频冷启动效果的核心指标之一。

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

四、逐步推导

1.关联两表并过滤24小时内播放记录

将发布表和播放表通过video_id JOIN，然后过滤play_time在 [publish_time, publish_time + 24h) 范围内的记录。

执行SQL

select
    p.video_id,
    p.author_id,
    p.publish_time,
    pl.user_id,
    pl.play_time
from t1_video_publish p
inner join t1_video_play pl
    on p.video_id = pl.video_id
where pl.play_time >= p.publish_time
  and pl.play_time < date_add(p.publish_time, interval 1 day)
order by p.video_id, pl.play_time

执行结果

+----------+-----------+----------------------+---------+----------------------+
| video_id | author_id |     publish_time     | user_id |      play_time       |
+----------+-----------+----------------------+---------+----------------------+
| V001     | a001      | 2024-06-01 08:00:00  | u001    | 2024-06-01 08:30:00  |
| V001     | a001      | 2024-06-01 08:00:00  | u002    | 2024-06-01 09:00:00  |
| V001     | a001      | 2024-06-01 08:00:00  | u003    | 2024-06-02 07:00:00  |
| V002     | a002      | 2024-06-01 10:00:00  | u001    | 2024-06-01 12:00:00  |
| V002     | a002      | 2024-06-01 10:00:00  | u002    | 2024-06-02 08:00:00  |
| V003     | a001      | 2024-06-02 12:00:00  | u003    | 2024-06-02 13:00:00  |
| V003     | a001      | 2024-06-02 12:00:00  | u005    | 2024-06-02 20:00:00  |
+----------+-----------+----------------------+---------+----------------------+

注意：V001的u004播放记录（2024-06-02 10:00:00）超过了发布时间+24h（截止到2024-06-02 08:00:00），被排除。V003的u001播放记录（2024-06-03 13:00:00）也超过了24h，被排除。

2.按视频聚合统计24小时内播放量

在过滤后的结果上按video_id GROUP BY，计数得到每个视频的24小时内播放量。

执行SQL

select
    p.video_id,
    p.author_id,
    p.publish_time,
    count(pl.user_id) as play_cnt_24h
from t1_video_publish p
left join t1_video_play pl
    on p.video_id = pl.video_id
    and pl.play_time >= p.publish_time
    and pl.play_time < date_add(p.publish_time, interval 1 day)
group by p.video_id, p.author_id, p.publish_time
order by play_cnt_24h desc

执行结果

+----------+-----------+----------------------+---------------+
| video_id | author_id |     publish_time     | play_cnt_24h  |
+----------+-----------+----------------------+---------------+
| V001     | a001      | 2024-06-01 08:00:00  | 3             |
| V002     | a002      | 2024-06-01 10:00:00  | 2             |
| V003     | a001      | 2024-06-02 12:00:00  | 2             |
+----------+-----------+----------------------+---------------+

使用LEFT JOIN保证了即使某个视频在24小时内没有任何播放记录，也会输出一行（play_cnt_24h=0），不会漏掉数据。

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE IF NOT EXISTS t1_video_publish (
    video_id STRING COMMENT '视频ID',
    author_id STRING COMMENT '作者ID',
    publish_time STRING COMMENT '发布时间'
);

CREATE TABLE IF NOT EXISTS t1_video_play (
    video_id STRING COMMENT '视频ID',
    user_id STRING COMMENT '播放用户ID',
    play_time STRING COMMENT '播放时间'
);

--数据插入
INSERT INTO t1_video_publish(video_id, author_id, publish_time) VALUES
('V001', 'a001', '2024-06-01 08:00:00'),
('V002', 'a002', '2024-06-01 10:00:00'),
('V003', 'a001', '2024-06-02 12:00:00');

INSERT INTO t1_video_play(video_id, user_id, play_time) VALUES
('V001', 'u001', '2024-06-01 08:30:00'),
('V001', 'u002', '2024-06-01 09:00:00'),
('V001', 'u003', '2024-06-02 07:00:00'),
('V001', 'u004', '2024-06-02 10:00:00'),
('V002', 'u001', '2024-06-01 12:00:00'),
('V002', 'u002', '2024-06-02 08:00:00'),
('V003', 'u003', '2024-06-02 13:00:00'),
('V003', 'u005', '2024-06-02 20:00:00'),
('V003', 'u001', '2024-06-03 13:00:00');

📱关注公众号

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

💬加群交流

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

一、题目​

三、思路分析​

四、逐步推导​

1.关联两表并过滤24小时内播放记录​

2.按视频聚合统计24小时内播放量​

五、常见坑点​

六、举一反三​

七、知识点总结​

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

你可能还想看