题解 | 最受欢迎的top3课程

with tb1 as (
    select
        avg(score) as avg_score,
        sum(timestampdiff(minute, start_time, end_time)) as time_len,
        count(p.id) as pv,
        p.cid
    from
        play_record_tb p
    join
        course_info_tb c on p.cid = c.cid
    where   
        timestampdiff(day, c.release_date, p.start_time) <= 7
    group by
        cid
),
tb2 as (
    select  
        row_number() over(order by pv desc, time_len desc) as rk,
        cid,
        pv,
        time_len
    from
        tb1
)
select
    cid,
    pv,
    time_len
from
    tb2
where
    rk <= 3