/*注意：窗口函数不能嵌套，窗口函数中的列必须出现在同层级select中，非聚合列必须在group by中，where和having都不能接窗口函数
连表：cid,pv,rk-----连接course_info_tb和play_record_tb
计数：观看次数n大于1---重复次数记为n
排序：按排名，次数相同时，按发布时间desc
筛选：n>1且重复观看排名次数排名前三（若没有三个输出全部）
*/
select t3.cid,t3.pv,t3.rk
from(select t2.cid
,t2.pv
,t2.release_date
,row_number() over(order by t2.pv desc,t2.release_date desc) as rk
from
(select t1.cid
,t1.repeat_cnt
,sum(t1.repeat_cnt) over(partition by t1.cid) as pv
,max(t1.release_date) as release_date
from(
select p.cid
,p.uid
,c.release_date
,count(*) as repeat_cnt
from course_info_tb c
join play_record_tb p on c.cid = p.cid
group by p.cid,p.uid,c.release_date
) t1
where t1.repeat_cnt > 1
group by t1.cid,t1.repeat_cnt
) t2
group by t2.cid,t2.pv,t2.release_date
) t3
where t3.rk <= 3
group by t3.cid,t3.pv
order by rk