with s1 as
(
    select uid, cid, count(*) as pv_per_user
    from play_record_tb prt
    group by cid, uid
    having pv_per_user > 1
),
s2 as
(
select 
    a.cid,
    release_date,
    sum(pv_per_user) over(partition by a.cid) as pv
from s1 a left join course_info_tb b
on a.cid = b.cid
order by pv desc, release_date desc
limit 3
)
select 
    cid,
    pv,
    row_number() over() as rk
from s2
;