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

方法一：

-- 第一步：筛选观看次数大于1的视频
with selected_play as (
select uid,cid,count(*) as cnt
from play_record_tb
group by uid,cid 
having count(*) >= 2)

/* 第二步：与course_info联合，获取到release_date
select c.cid,c.release_date,s.cnt as pv
from course_info_tb c inner join selected_play s on c.cid = s.cid */

-- 第三步：row_number()排序，输出
select cid,round(pv*1.0,3),rk 
from 
(
select cid,pv,row_number() over(order by pv desc,release_date desc) as rk
from(select c.cid,c.release_date,s.cnt as pv
from course_info_tb c inner join selected_play s on c.cid = s.cid) t1) t2
where rk <= 3
order by rk