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

with
t1 as(
    select
        uid,
        cid,
        release_date,
        count(uid) as watch_times
    from
        play_record_tb left join course_info_tb using(cid)
    group by
        uid,
        cid,
        release_date
    having
        count(uid)>=2
)
,t2 as(
    select
        cid,
        release_date,
        sum(watch_times) as pv
    from
        t1
    group by
        cid,
        release_date
)
,t3 as(
    select
        cid,
        release_date,
        pv,
        rank()over(order by pv desc,release_date desc,cid) as rk
    from
        t2
    order by
        rk
)

select
    cid,
    pv,
    rk
from
    t3
where
    rk<=3
order by
    rk