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

SELECT cid , concat(pv,'.000') , ROW_NUMBER()over(order by pv desc, release_date desc ) as rk from (
SELECT t1.cid,t1.uid ,  t1.count_cid_uid  as pv, course_info_tb.release_date from (
SELECT cid,uid , count(uid) as count_cid_uid  from play_record_tb
group by cid,uid
)t1
left join course_info_tb  on t1.cid = course_info_tb.cid
where count_cid_uid  > 1)t2
limit 3