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

WITH
    c AS (
        SELECT
            b.uid,
            b.cid,
            COUNT(b.id) AS pp
        FROM
            play_record_tb b
        GROUP BY
            b.uid,
            b.cid
        HAVING
            COUNT(b.id) <> 1
    ),
    ranked AS (
        SELECT
            cid,
            SUM(pp) AS pv
        FROM
            c
        GROUP BY
            cid
    )
SELECT
    t2.cid,
    pv,
    row_number() over (
        order by
            pv desc,
            t3.release_date desc
    ) as rk
FROM
    ranked t2 
     join course_info_tb t3
    on t2.cid=t3.cid
limit
    3