题解 | 被重复观看次数最多的3个视频
被重复观看次数最多的3个视频
https://www.nowcoder.com/practice/b75fa2412659422c96369976ee1f9504
WITH t1 AS (
SELECT
uid,
cid,
count(*) pv
FROM play_record_tb
GROUP BY uid, cid
HAVING count(*) > 1)
SELECT
t1.cid as cid,
format(t1.pv,3) as pv,
row_number() OVER(order by t1.pv DESC, release_date DESC) as rk
FROM course_info_tb c JOIN t1 USING(cid)
ORDER BY rk
LIMIT 3
