select cid,pv,
row_number()over(order by pv desc,release_date desc) as rk
from
(
select c.cid as cid,release_date,pv from course_info_tb as c
join
(
select cid,sum(count_1) as pv from
(
select uid,cid,count(1) as count_1 from play_record_tb
group by uid,cid
having count(1)>1) as t1
group by cid) as t2
on c.cid = t2.cid) as t3
order by pv desc,release_date desc
limit 3