【美团】查询高回头率视频
JOIN 连接聚合函数GROUP BY窗口函数HAVINGORDER BY用户行为分析排行榜面试真题
题目描述
来源：美团 SQL 面试真题
课程平台希望找出能让用户一遍接一遍重复观看的高回头率视频，并统计每个视频带来的重复观看次数。
数据表
course_info_tb 课程信息表
| 字段 | 类型 | 说明 |
|---|---|---|
| id | BIGINT | 课程记录 ID，主键 |
| cid | BIGINT | 课程 ID |
| tag | VARCHAR | 视频类别 |
| release_date | DATE | 发布日期 |
| duration | INT | 视频时长 |
play_record_tb 用户观看记录表
| 字段 | 类型 | 说明 |
|---|---|---|
| id | BIGINT | 观看记录 ID，主键 |
| uid | BIGINT | 用户 ID |
| cid | BIGINT | 课程 ID |
| start_time | DATETIME | 开始观看时间 |
| end_time | DATETIME | 结束观看时间 |
| score | INT | 用户评分 |
重复观看口径
- 先按
uid + cid统计每位用户观看每个视频的次数。 - 同一用户对同一视频只观看 1 次时，不计入该视频的重复观看次数。
- 同一用户对同一视频观看
n次且n > 1时，为该视频贡献n次重复观看。 - 视频的
pv是所有重复观看用户贡献次数之和。 - 只输出存在重复观看的课程。
题目要求
- 按
pv降序排名。 pv相同时，发布日期越晚的视频排名越靠前。- 如前两项仍相同，使用
cid升序作为稳定排序条件。 - 取排名前三的视频；不足三个时全部输出。
- 结果按排名升序排列。
期望输出列
| 列名 | 说明 |
|---|---|
| cid | 课程 ID |
| pv | 被重复观看次数 |
| rk | 视频排名 |
数据样例
| idPKBIGINT | cidBIGINT | tagVARCHAR(32) | release_dateDATE | durationINT |
|---|---|---|---|---|
| 1 | 9001 | sql | 2022-01-01 | 60 |
| 2 | 9002 | sql | 2022-01-01 | 90 |
| 3 | 9003 | sql | 2022-01-01 | 45 |
| 4 | 9004 | java | 2022-01-02 | 45 |
输入数据显示 4 / 4 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里