题解 | 最受欢迎的top3课程

select cid,
pv,
time_len
from (
    select cid,
    pv,
    time_len,
    row_number() over (order by pv desc, time_len desc) rk
    from (
        select c.cid,
        count(*) pv,
        sum(timestampdiff(minute, start_time, end_time)) time_len
        from course_info_tb c
        join play_record_tb p using (cid)
        where date_add(release_date,interval 1 week)>date(start_time)
        group by c.cid
        having avg(score)>=3
    )t1
    
) t2
where rk<=3
order by rk