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

SELECT
    cid,
    pv,
    ROW_NUMBER() OVER(ORDER BY pv DESC, release_date DESC) AS rk # 3. 排名，注意顺序
FROM(
    SELECT
        cid,
        release_date,
        SUM(view_cnt) AS pv # 2. 统计视频总重复观看次数
    FROM(
        SELECT
            p.uid,
            p.cid,
            release_date,
            COUNT(end_time) AS view_cnt # 1.找到每个用户观看视频次数
        FROM play_record_tb p
        JOIN course_info_tb c
        ON p.cid = c.cid
        GROUP BY uid, cid, release_date
        HAVING view_cnt != 1 # 排除观看次数只有一次的
    ) AS tb1
    GROUP BY cid, release_date
) AS tb2
LIMIT 3 # 4.排名靠前的3个