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

with revideo as (
    select
    p.uid,
    p.cid,
    c.release_date,
    round(count(*)*1.0, 3) as pv,
    row_number() over(order by count(p.cid) desc,c.release_date desc) as rk
    from course_info_tb c
    join play_record_tb p on c.cid=p.cid
    group by cid,p.uid,release_date
)

select
cid,
pv,
rk
from revideo
where pv>1 and rk<=3
order by rk;

感觉自己分组条件时常不清晰，菜就多练