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

SELECT
    sub.cid,
    ROUND(sub.total_pv, 3) AS pv,
    sub.rk
FROM (
    SELECT
        cid,
        SUM(user_pv) AS total_pv,
        ROW_NUMBER() OVER (
            ORDER BY 
                SUM(user_pv) DESC,
                release_date DESC
        ) AS rk
    FROM (
        SELECT
            p.cid,
            uid,
            COUNT(*) AS user_pv,
            c.release_date
        FROM play_record_tb p
        JOIN course_info_tb c ON p.cid = c.cid
        GROUP BY p.cid, uid, c.release_date  
        HAVING COUNT(*) > 1
    ) AS user_stats
    GROUP BY cid, release_date  
) AS sub
WHERE sub.rk <= 3
ORDER BY sub.rk ASC;

自己不会写，学习一下评论区大佬的代码

要一层一层写，不要总想一次写完，多写点子查询表逻辑更清楚

1. 按用户课程统计播放次数
2. 聚合为课程级别的播放量
3. 筛出排名前 3 的课程