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

select l.cid,round(l.s,3) as pv,rk
from (select prt.*, row_number() over (order by s desc ,release_date desc) as rk
      from course_info_tb ct
               join (select a.cid, sum(a.c) as s
                     from (select uid, cid, count(*) as c
                           from play_record_tb
                           group by uid, cid) a
                     where c > 1
                     group by cid) prt on ct.cid = prt.cid) as l
where rk < 4;