题解 | 最受欢迎的top3课程

select
        cid,
        pv,
        time_len
from (
    select
        prt.cid,
        avg(score) as avg_score,
        count(if(date(prt.start_time) < date_add(cit.release_date,interval 1 week),prt.start_time,null)) as pv,
        sum(timestampdiff(minute,if(date(prt.start_time) < date_add(cit.release_date,interval 1 week),prt.start_time,0),if(date(prt.end_time) < date_add(cit.release_date,interval 1 week),prt.end_time,0))) as time_len
    from 
            play_record_tb as prt
    left join 
            course_info_tb as cit
    on 
            prt.cid = cit.cid
    group by 
            prt.cid 
)a
where 
        avg_score >= 3
order by 
        pv desc,
        time_len desc
limit 3