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

#计算每个用户对每个视频的观看次数，并且筛选出观看次数大于1的
WITH play AS (
    SELECT
        uid,
        cid,
        COUNT(*) as play_count
    FROM
        play_record_tb
    GROUP BY
        uid,
        cid
    HAVING
        COUNT(*)>1
),
#计算重复观看次数
repeat_play AS (
    SELECT
        cid,
        SUM(play_count) AS pv
    FROM
        play        
    GROUP BY
        cid
),
#排序编号
pv_ranked AS (SELECT
    r.cid,
    r.pv,
    ROW_NUMBER() OVER (ORDER BY r.pv DESC, c.release_date DESC) AS rk
FROM
    repeat_play AS r
    INNER JOIN
    course_info_tb AS c ON r.cid=c.cid)
#输出最终结果
SELECT
    cid,
    pv,
    rk
FROM
    pv_ranked
WHERE
    rk<=3
ORDER BY
    rk ASC;