题解 | 被重复观看次数最多的3个视频

-- 第一步：分组统计 + 排名
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;


这个还得再看看