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

select cid, pv, rk
from (
    select t2.cid,
    pv,
    row_number() over (order by pv desc, release_date desc) rk
    from (
        select cid,
        sum(num) pv
        from (
            select cid, uid, count(*) num
            from play_record_tb
            group by cid, uid
            having count(*)>1
        ) t1
        group by cid
    ) t2
    join course_info_tb c
    on t2.cid=c.cid
) t
where rk<=3
order by rk