快手大数据面试SQL-热门话题Top 10及参与用户数

一、题目

已知有表t1_topic_video记录了视频与话题的关联关系（一个视频可以带多个话题标签），包含字段：video_id（视频ID）、topic_id（话题ID）、topic_name（话题名称）、author_id（作者ID）、create_time（视频创建时间）。

请统计参与视频数量最多的Top 10话题，输出话题ID、话题名称、参与视频数、参与作者数（去重），按参与视频数降序排列。如果视频数相同，按话题ID升序排列。

样例数据

+----------+----------+--------------+-----------+----------------------+
| video_id | topic_id | topic_name   | author_id |     create_time      |
+----------+----------+--------------+-----------+----------------------+
| V001     | T001     | #挑战30天    | a001      | 2024-06-01 08:00:00  |
| V002     | T001     | #挑战30天    | a002      | 2024-06-01 09:00:00  |
| V003     | T002     | #夏日穿搭    | a001      | 2024-06-01 10:00:00  |
| V004     | T001     | #挑战30天    | a001      | 2024-06-01 11:00:00  |
| V005     | T003     | #美食探店    | a003      | 2024-06-01 12:00:00  |
| V006     | T002     | #夏日穿搭    | a004      | 2024-06-01 13:00:00  |
| V007     | T003     | #美食探店    | a002      | 2024-06-01 14:00:00  |
| V008     | T001     | #挑战30天    | a003      | 2024-06-01 15:00:00  |
| V009     | T004     | #萌宠日常    | a005      | 2024-06-01 16:00:00  |
| V010     | T002     | #夏日穿搭    | a002      | 2024-06-01 17:00:00  |
| V011     | T005     | #健身打卡    | a006      | 2024-06-01 18:00:00  |
| V012     | T003     | #美食探店    | a004      | 2024-06-01 19:00:00  |
| V013     | T001     | #挑战30天    | a004      | 2024-06-01 20:00:00  |
+----------+----------+--------------+-----------+----------------------+

三、思路分析

本题是对GROUP BY + COUNT DISTINCT的经典考察，难度不高但覆盖了数据分析的核心模式。

核心步骤：

• 按话题分组聚合：使用 GROUP BY topic_id, topic_name，统计每个话题下的视频数 COUNT(video_id) 和参与作者数 COUNT(DISTINCT author_id)。

• 排序取Top 10：使用 ORDER BY video_cnt DESC, topic_id ASC，配合 LIMIT 10 取出前10条。

• 注意去重：作者数需要去重，因为同一个作者可能发布多个视频参与同一话题。例如a001发布了V001和V004都带#挑战30天标签，计数时只算1个作者。

在快手业务中，热门话题排行榜是内容运营和推荐算法的重要参考，直接影响流量分发策略。

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

四、逐步推导

1.按话题统计视频数和参与作者数

使用GROUP BY按topic_id和topic_name聚合，COUNT统计视频数，COUNT DISTINCT统计去重作者数。

执行SQL

select
    topic_id,
    topic_name,
    count(video_id) as video_cnt,
    count(distinct author_id) as author_cnt
from t1_topic_video
group by topic_id, topic_name
order by video_cnt desc, topic_id asc

执行结果

+----------+--------------+------------+-------------+
| topic_id | topic_name   | video_cnt  | author_cnt  |
+----------+--------------+------------+-------------+
| T001     | #挑战30天    | 5          | 3            |
| T002     | #夏日穿搭    | 3          | 3            |
| T003     | #美食探店    | 3          | 3            |
| T004     | #萌宠日常    | 1          | 1            |
| T005     | #健身打卡    | 1          | 1            |
+----------+--------------+------------+-------------+

2.取Top 10热门话题

添加LIMIT 10限制，得到最终结果。

执行SQL

select
    topic_id,
    topic_name,
    count(video_id) as video_cnt,
    count(distinct author_id) as author_cnt
from t1_topic_video
group by topic_id, topic_name
order by video_cnt desc, topic_id asc
limit 10

执行结果

+----------+--------------+------------+-------------+
| topic_id | topic_name   | video_cnt  | author_cnt  |
+----------+--------------+------------+-------------+
| T001     | #挑战30天    | 5          | 3            |
| T002     | #夏日穿搭    | 3          | 3            |
| T003     | #美食探店    | 3          | 3            |
| T004     | #萌宠日常    | 1          | 1            |
| T005     | #健身打卡    | 1          | 1            |
+----------+--------------+------------+-------------+

结果：#挑战30天以5个视频、3位作者参与位居榜首；#夏日穿搭和#美食探店并列第二（均为3个视频）。

3.(可选) 计算话题的平均视频/作者比

如果想进一步分析话题的作者参与度（人均发布视频数），可以增加计算 人均视频数 = 视频数 / 作者数。

执行SQL

select
    topic_id,
    topic_name,
    count(video_id) as video_cnt,
    count(distinct author_id) as author_cnt,
    round(count(video_id) * 1.0 / count(distinct author_id), 2) as avg_video_per_author
from t1_topic_video
group by topic_id, topic_name
order by video_cnt desc, topic_id asc

执行结果

+----------+--------------+------------+-------------+----------------------+
| topic_id | topic_name   | video_cnt  | author_cnt  | avg_video_per_author |
+----------+--------------+------------+-------------+----------------------+
| T001     | #挑战30天    | 5          | 3            | 1.67                 |
| T002     | #夏日穿搭    | 3          | 3            | 1.00                 |
| T003     | #美食探店    | 3          | 3            | 1.00                 |
| T004     | #萌宠日常    | 1          | 1            | 1.00                 |
| T005     | #健身打卡    | 1          | 1            | 1.00                 |
+----------+--------------+------------+-------------+----------------------+

五、常见坑点

坑1：RANK vs DENSE_RANK vs ROW_NUMBER — 并列值处理不同：ROW_NUMBER硬区分，RANK跳跃，DENSE_RANK连续。统计"前N名"需明确业务倾向。

坑2：NULL值排序位置 — ORDER BY 默认 NULLS LAST，如果排序列有 NULL，Top N可能被挤掉。

六、举一反三

1. 分组Top N：PARTITION BY category 实现每个品类/区域的前N名

2. 含占比的排名：SUM() OVER() 算总量，每个条目除总量得贡献度

3. 滚动时间窗口Top N：限定过去30天数据，更适合分析趋势变化

七、知识点总结

考点	说明
COUNT DISTINCT 去重	统计唯一用户/事件数，避免重复记录干扰聚合结果
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE IF NOT EXISTS t1_topic_video (
    video_id STRING COMMENT '视频ID',
    topic_id STRING COMMENT '话题ID',
    topic_name STRING COMMENT '话题名称',
    author_id STRING COMMENT '作者ID',
    create_time STRING COMMENT '视频创建时间'
);

--数据插入
INSERT INTO t1_topic_video(video_id, topic_id, topic_name, author_id, create_time) VALUES
('V001', 'T001', '#挑战30天', 'a001', '2024-06-01 08:00:00'),
('V002', 'T001', '#挑战30天', 'a002', '2024-06-01 09:00:00'),
('V003', 'T002', '#夏日穿搭', 'a001', '2024-06-01 10:00:00'),
('V004', 'T001', '#挑战30天', 'a001', '2024-06-01 11:00:00'),
('V005', 'T003', '#美食探店', 'a003', '2024-06-01 12:00:00'),
('V006', 'T002', '#夏日穿搭', 'a004', '2024-06-01 13:00:00'),
('V007', 'T003', '#美食探店', 'a002', '2024-06-01 14:00:00'),
('V008', 'T001', '#挑战30天', 'a003', '2024-06-01 15:00:00'),
('V009', 'T004', '#萌宠日常', 'a005', '2024-06-01 16:00:00'),
('V010', 'T002', '#夏日穿搭', 'a002', '2024-06-01 17:00:00'),
('V011', 'T005', '#健身打卡', 'a006', '2024-06-01 18:00:00'),
('V012', 'T003', '#美食探店', 'a004', '2024-06-01 19:00:00'),
('V013', 'T001', '#挑战30天', 'a004', '2024-06-01 20:00:00');

📱关注公众号

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

💬加群交流

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