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

with temp as (
    select
        cid,
        count(*) as watch_cnt
        from play_record_tb 
        group by uid , cid
        having count(*) > 1
),

temp1 as (
    select 
    cid,
    sum(watch_cnt) as total_pv
    from temp
    group by cid
)

select
    tp1.cid,
    round(tp1.total_pv * 1.0 , 3) as pv,
    row_number() over(order by tp1.total_pv desc , c.release_date DESC) as rk 
    FROM temp1 tp1
    JOIN course_info_tb c 
    ON tp1.cid = c.cid
    ORDER BY rk ASC
    LIMIT 3;