题解 | 最受欢迎的top3课程

with popular_amount as(
    select
        c.cid as cid,
        count(uid) as pv,
        sum(timestampdiff(minute, start_time, end_time)) as time_len
    from 
        course_info_tb c 
        join play_record_tb p on c.cid = p.cid
    where 
        timestampdiff(day, release_date, date(end_time)) < 7
    group by 
        cid
    having 
        avg(score) >= 3
),
popular_rank as (
    select
        cid,
        pv,
        time_len,
        row_number() over(order by pv desc, time_len desc) as rk
    from 
        popular_amount
)
select 
    cid,
    pv,
    time_len
from popular_rank
where rk <= 3

1. 按cid分组，筛选出是平均评分不低于3（>=3），发布后一周内被观看的视频，随后计算被观看次数与被观看时长
2. 按被观看次数与被观看时长排序，使用窗口函数得到排名
3. 筛选出排名<=3，即为TOP3