题解 | 最受欢迎的top3课程

select 
    a.cid,
    count(b.uid) as pv,
    ifnull(sum(b.len),0) as time_len
from 
    course_info_tb a
    left join (
        select 
            a1.uid,
            a1.cid,
            timestampdiff(minute,a1.start_time,a1.end_time) as len,
            a1.score
        from 
            play_record_tb a1
            left join course_info_tb b1 on a1.cid = b1.cid
        where 
            a1.start_time <= date_add(b1.release_date, interval 7 day) 
    ) b on a.cid = b.cid
group by 
    a.cid
having
    avg(len) >= 3
order by 
    pv desc,
    time_len desc
limit 
    3

注意：题目要求的时限是一周内，也就是说计算观看次数和总播放时长都是按照视频发布后一周内的数据进行计算。