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

WITH a AS (
    SELECT uid, cid, COUNT(*) AS rn
    FROM play_record_tb
    GROUP BY uid, cid
),
b AS (
    SELECT 
        a.cid,
        SUM(CASE WHEN rn > 1 THEN rn ELSE 0 END) AS rk,
        c.release_date AS time
    FROM a 
    JOIN course_info_tb c ON a.cid = c.cid
    GROUP BY a.cid, c.release_date  
)
SELECT 
    cid,
    rk,
    ROW_NUMBER() OVER (ORDER BY rk DESC, time desc) AS ranking  
FROM b
ORDER BY ranking
limit 3;