with
    t1 as (
        SELECT
            uid,
            c.cid,
            release_date,
            case
                when count(*) > 1 then count(*)
                else 0
            end as repeat_watch_cnts
        FROM
            course_info_tb c
            JOIN play_record_tb p ON c.cid = p.cid
        GROUP BY
            1,
            2,
            3
    ),
    t2 as (
        SELECT
            cid,
            release_date,
            sum(repeat_watch_cnts) as pv
        FROM
            t1
        group by
            1,
            2
    )
SELECT
    *
from
    (
        SELECT
            cid,
            pv,
            ROW_NUMBER() over (
                order by
                    pv desc,
                    release_date desc
            ) as rk
        FROM
            t2
    ) a
where
    rk <= 3;