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

select* 
from
    (select t2.cid
    ,pv
    ,row_number() over(order by pv desc,t3.release_date desc) as rk
    from 
        (select cid
            ,round(sum(view_time),3) as pv
            from
            (select uid,cid
                ,count(*)  as view_time
                from play_record_tb
                group by uid,cid
                having view_time>1
            )t1
            group by cid
        )t2
        left join
        (select cid
            ,release_date 
            from course_info_tb
        )t3 
        on t2.cid=t3.cid
    )t4
where rk<=3

思路;

1、以cid，uid分组聚合，找出每个人每个视频的观看量view_time，同时having筛选删去只有1次的view_time，为表t1

2、以cid分组聚合，计算每个视频的重复观看量pv，round保留三位小数，为表t2

3、left join链接course_info_tb表每个视频的发布日期release_date ，形成表t3

4、题目规定排名之间没有并列排序，所以用row_number函数，排序规则是按照pv降序，再按release_date降序排列

5、只保留前三行，限制rk<=3