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

select
    ct.cid,
    t2.pv,
    row_number() over (
        order by
            pv desc,
            release_date desc
    ) as rk
from
    course_info_tb ct
    join (
        select
            cid,
            sum(visit_count) as pv
        from
            (
                select
                    uid,
                    cid,
                    count(uid) as visit_count
                from
                    play_record_tb
                group by
                    uid,
                    cid
                having
                    visit_count > 1
            ) t1
        group by
            cid
    ) t2 using (cid)
order by
    rk
limit
    3