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

#建临时表，寻找重复播放次数
with r as (
    select 
        p.cid as cid, 
        count(p.cid) as pv
from play_record_tb as p
    left join course_info_tb as c on p.cid = c.cid
group by p.cid,p.uid, c.release_date
#按照观看者id和视频id分组，寻找观看次数>1的人，计算视频播放次数-1>1的计入，按照上传时间分组，为下面的排序准备
having count((p.cid) - 1) > 1
order by pv , c.release_date desc
)
select cid, cast(pv as decimal(10,3)) as pv, 
    row_number() over(order by pv desc) as rk
from r 
limit 3