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

WITH cid_view_summary AS (
    SELECT
        t.cid,
        SUM(t.view_people_num) AS pv
    FROM (
        SELECT
            COUNT(uid) AS view_people_num,
            cid
        FROM play_record_tb
        GROUP BY cid, uid
        HAVING COUNT(uid) > 1
    ) t
    GROUP BY t.cid
)

SELECT 
    cid_view_summary.cid,
    pv,
    ROW_NUMBER() OVER (ORDER BY pv DESC, release_date DESC) AS rn
FROM cid_view_summary JOIN course_info_tb
ON cid_view_summary.cid = course_info_tb.cid
ORDER BY rn
LIMIT 3