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

select t2.cid, t2.pv, 
       row_number() over(order by t2.pv desc, 
                        course_info_tb.release_date desc) rk
from (select cid, sum(cnt) pv
      from (select cid, uid, count(cid) cnt
            from play_record_tb
            group by cid, uid
            having cnt <> 1) t1
      group by cid) t2
left join course_info_tb 
on t2.cid = course_info_tb.cid
limit 3;