题解 | 输出播放量最高的视频
输出播放量最高的视频
https://www.nowcoder.com/practice/9e9cb264e1f64e28846975d5a32ba8e4
WITH general AS (
SELECT a.id, a.cid, a.start_time,
SUM(IF(a.start_time BETWEEN b.start_time AND b.end_time, 1, 0)) AS cnt
FROM play_record_tb AS a
LEFT JOIN play_record_tb AS b
ON a.cid = b.cid
GROUP BY a.id, a.cid, a.start_time
)
SELECT cid, ROUND(MAX(cnt), 3) AS max_peak_uv
FROM general
GROUP BY cid
ORDER BY max_peak_uv DESC
LIMIT 3;
