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

select
    *
from
    (
        select
            cid,
            count(uid) + sum(repeat_times) as pv,
            row_number() over (
                order by
                    (count(uid) + sum(repeat_times)) desc,
                    release_date desc
            ) rk
        from
            (
                select
                    prt.uid,
                    prt.cid,
                    cit.release_date,
                    count(prt.uid) - 1 as repeat_times
                from
                    play_record_tb prt
                inner join course_info_tb cit on prt.cid = cit.cid
                group by
                    prt.uid,
                    prt.cid,
                    cit.release_date
                having
                    repeat_times >= 1
            ) visit_times
        group by
            cid,
            release_date
    ) total
where 
    total.rk <= 3

符合问题概念描述的题解