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

select
    t.cid,
    sum(t.pv) as pv,
    row_number() over(order by sum(t.pv) desc,c.release_date desc) as rk
from
    (select
        p.cid,
        count(p.uid) as pv
    from 
        play_record_tb p
    group by 
        p.cid,p.uid
    having count(p.uid)>1
    ) t
left join 
    course_info_tb c 
on 
    t.cid=c.cid
group by   
    t.cid,t.pv,c.release_date
order by 
    row_number() over(order by sum(t.pv) desc,c.release_date desc) asc 
limit 3