题解 | 被重复观看次数最多的3个视频
被重复观看次数最多的3个视频
https://www.nowcoder.com/practice/b75fa2412659422c96369976ee1f9504
-- 第一步：分组统计 + 排名
WITH sub AS (
SELECT
p.cid,
ROUND(COUNT(*) * 1.0, 3) AS pv,
ROW_NUMBER() OVER (
ORDER BY COUNT(*) DESC, c.release_date DESC
) AS rk
FROM
course_info_tb c
JOIN play_record_tb p
ON c.cid = p.cid
GROUP BY
p.cid,
p.uid,
c.release_date
)
-- 第二步：过滤结果并输出
SELECT
cid,
pv,
rk
FROM sub
WHERE pv > 1 AND rk <= 3
ORDER BY rk ASC;
这个还得再看看
查看12道真题和解析