题解 | 最受欢迎的top3课程

with
t1 as(
    select
        cid,
        count(cid) as pv,
        sum(timestampdiff(minute,start_time,end_time)) as time_len
    from
        play_record_tb left join course_info_tb using(cid)
    where
        timestampdiff(day,release_date,date_format(start_time,'%Y-%m-%d'))<7
    group by
        cid
    having
        avg(score)>=3
)

select * from t1 order by pv desc,time_len desc limit 3