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

#先看每个用户的每个视频的观看次数,并剔除每个视频每个用户观看次数为1次的数据，再进行表连接得到发布日期，并对每个视频的观看次数求和（此时已经剔除观看次数为1的数据了）；最后使用窗口函数对播放次数进行排名，此处不需要进行分组，只需按照次数和发布时间排名即可
select
    cid,
    pv,
    row_number()over(order by pv desc, t2.release_date desc) as rk
from(
select
    cid,
    release_date,
    sum(count_num) as pv
from (
select
    uid,
    cid,
    count(cid) as count_num
from play_record_tb
group by uid,cid
having count_num >1
) t1
left join course_info_tb  using(cid)
group by cid,release_date
) t2

limit 3