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

WITH
    course_play_rank AS (
        -- 第一步：先计算每个课程+用户的播放次数，并生成排名
        SELECT
            prt.cid,
            CAST(COUNT(*) AS DECIMAL(10, 3)) AS pv, -- 每个用户对每个课程的播放次数
            ROW_NUMBER() OVER (
                ORDER BY
                    COUNT(*) DESC,
                    cit.release_date DESC
            ) AS rk
        FROM
            course_info_tb cit
            LEFT JOIN play_record_tb prt ON cit.cid = prt.cid
        GROUP BY
            prt.cid,
            cit.release_date,
            prt.uid -- 补充release_date到分组（避免函数依赖错误）
    )
    -- 第二步：在外层筛选符合条件的数据
SELECT
    cid,
    pv,
    rk
FROM
    course_play_rank
WHERE
    pv > 1
    AND rk < 4;