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

 -- 第一步：统计每个用户对每个课程的观看次数
WITH
    user_course_views AS (
        SELECT
            pr.cid,
            pr.uid,
            COUNT(*) AS view_count
        FROM
            play_record_tb pr
        GROUP BY
            pr.cid,
            pr.uid
    ),
    -- 第二步：筛选出观看次数大于 1 的课程和用户组合，统计每个课程的重复观看人次数
    repeat_views AS (
        SELECT
            uc.cid,
            SUM(uc.view_count) AS pv
        FROM
            user_course_views uc
        WHERE
            uc.view_count > 1
        GROUP BY
            uc.cid
    ),
    -- 第三步：结合课程信息表，按重复观看人次数降序排序，若相同则按发布日期降序排序
    ranked_views AS (
        SELECT
            rv.cid,
            rv.pv,
            -- 第四步：为排序后的课程添加排名
            ROW_NUMBER() OVER (
                ORDER BY
                    rv.pv DESC,
                    ci.release_date DESC
            ) AS rk
        FROM
            repeat_views rv
            JOIN course_info_tb ci ON rv.cid = ci.cid
    )
    -- 第五步：选取排名前三的课程
SELECT
    cid,
    ROUND(pv, 3) AS pv,
    rk
FROM
    ranked_views
WHERE
    rk <= 3
ORDER BY
    rk;