SQL大神带你飞 | 24春招京东SQL真题解析-最受欢迎的top3课程

最近做了京东的24年春招题，题目如下： alt

题目分析

在这道题目中，我们需要从两个表中提取数据：course_info_tb 和 play_record_tb。以下是每个表的结构及字段的汉语意思：

course_info_tb（课程信息表）
- cid：课程ID
- release_date：课程发布日期
play_record_tb（播放记录表）
- cid：课程ID
- start_time：播放开始时间
- end_time：播放结束时间
- score：评分

目标：找出发布后一周内播放次数多且平均评分不低于3的课程，按播放时长降序排列，输出前三名。

输出要求：输出课程ID、播放次数、播放总时长，按播放总时长降序排列，限制输出前三名。

知识点关键词：SQL连接、时间差计算、分组、聚合函数、排序、限制输出

解答步骤

连接表

我们需要将 course_info_tb 表和 play_record_tb 表连接在一起，以便获取每个播放记录的课程信息。使用 JOIN 语句通过 cid 字段连接两个表。
```
from course_info_tb c 
join play_record_tb p on p.cid = c.cid
```
过滤条件

使用 WHERE 子句过滤出在课程发布后一周内的播放记录。通过 TIMESTAMPDIFF 函数计算 release_date 和 start_time 之间的天数差。
```
where 
timestampdiff(day, c.release_date, date_format(p.start_time, '%Y-%m-%d')) <= 7
```
计算播放次数和总时长

使用 COUNT(*) 计算播放次数，使用 SUM 和 TIMESTAMPDIFF 计算播放总时长（以分钟为单位）。
```
count(*) as pv,
sum(timestampdiff(minute, p.start_time, p.end_time)) as time_len
```
分组和过滤

使用 GROUP BY 语句按课程ID分组，并使用 HAVING 子句过滤出平均评分不低于3的课程。
```
group by p.cid
having avg(p.score) >= 3
```
排序和限制输出

使用 ORDER BY 语句按播放总时长降序排列结果，并使用 LIMIT 限制输出前三名。
```
order by time_len desc
limit 3
```

完整代码

select p.cid,
count(*) as pv,
sum(timestampdiff(minute, p.start_time, p.end_time)) as time_len
from course_info_tb c 
join play_record_tb p on p.cid = c.cid
where 
    timestampdiff(day, c.release_date, date_format(p.start_time, '%Y-%m-%d')) <= 7
group by p.cid
having avg(p.score) >= 3
order by time_len desc
limit 3

近似题目练习推荐

每个月Top3的周杰伦歌曲

知识点：SQL连接、分组、排序、窗口函数

最长连续登录天数

知识点：时间差计算、窗口函数、分组、排序

获取指定客户每月的消费额

知识点：SQL连接、聚合函数、分组、日期函数