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

with t as(
    select cid,sum(watch) 'pv'
    from(
        select cid, count(*) 'watch'
        from play_record_tb p
        group by cid, uid
        having count(*) > 1
    )a
    group by cid
)

select cid, pv, rk
from (
    select t.cid, pv, row_number()over(order by pv desc, release_date desc )'rk'
    from t 
    join course_info_tb c on t.cid = c.cid
)b
where  rk <= 3;

• 首先先筛选出重复观看次数超过1次的cid

with t as(
    select cid,sum(watch) 'pv'
    from(
        select cid, count(*) 'watch'
        from play_record_tb p
        group by cid, uid
        having count(*) > 1
    )a
    group by cid
)

• 接着按照pv次数和 发布次数判断rk，最后筛选rk <= 3的cid 即可