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

这里其实用round格式化感觉有点多余
select cid, round(a.cnt*1.0,3) as pv, a.posn as rk
from(
    select p.cid,count(*) as cnt,row_number()over(order by count(*) desc,release_date desc) as posn
    from play_record_tb as p
    inner join course_info_tb as c on p.cid=c.cid
    where (uid,p.cid) in(
        select uid,cid
        from play_record_tb
        group by uid,cid
        having count(*)>1)
    group by p.cid,release_date
    having count(*)>1
) as a
where a.posn<=3
order by rk