select
t.cid,
sum(t.pv) as pv,
row_number() over(order by sum(t.pv) desc,c.release_date desc) as rk
from
(select
p.cid,
count(p.uid) as pv
from play_record_tb p
group by p.cid,p.uid
having count(p.uid)>1) t
left join course_info_tb c on t.cid=c.cid
group by t.cid,t.pv,c.release_date
order by row_number() over(order by sum(t.pv) desc,c.release_date desc) asc limit 3