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

with a as(
    select cid,
    count(*) as view_number
    from play_record_tb
    group by cid,uid
    having count(*)>1
),
b as(
    select cid,
    sum(view_number) as numbers
    from a
    group by cid
)
select *
from (
    select b.cid,
    cast(numbers as decimal(10,3)) as pv,
    row_number() over(order by numbers desc,release_date desc) as rk
    from b join course_info_tb using(cid)) temp
where rk<=3;