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

with t1 as (
    select uid,cid,count(*) as num
    from play_record_tb
    group by uid,cid
    having num >1), --因为一个人至少看过2次才算，所以过滤掉一人只看过一次的记录
t2 as (
    select t1.cid,sum(num) as pv,release_date --将多人观看同一视频的次数加起来
    from t1 left join course_info_tb as c using(cid)
    group by t1.cid,release_date)
select cid,pv,row_number() over (order by pv desc,release_date desc) as rk--如果被重复观看次数一样大，则越晚发布的视频排名越靠前，每个视频的排名为排在他前面的视频个数+1
from t2
limit 3;--只返回排名前三的视频