题解 | 被重复观看次数最多的3个视频
被重复观看次数最多的3个视频
https://www.nowcoder.com/practice/b75fa2412659422c96369976ee1f9504
SELECT
t1.cid,
pv,
ROW_NUMBER() OVER(ORDER BY pv DESC, ct.release_date DESC) rk
FROM
(SELECT
cid,
CAST(COUNT(*) AS DECIMAL(7,3)) AS pv
FROM play_record_tb
GROUP BY uid, cid
HAVING pv > 1
ORDER BY pv DESC ) t1
JOIN course_info_tb ct ON ct.cid = t1.cid
ORDER BY rk
LIMIT 3