题解 | 最受欢迎的top3课程

with t as (
    select uid, a.cid, score, release_date, start_time,
    round(timestampdiff(minute, start_time, end_time), 3) as diff
    from play_record_tb a join course_info_tb b on a.cid=b.cid
), t_avg_score as (
    select cid, avg(score) as avg_score
    from t
    group by cid
    having avg_score>=3
), t_pv as (
    select cid, count(1) as pv, sum(diff) as time_len
    from t
    where start_time<=DATE_ADD(release_date, interval 6 day)
    group by cid
)
select t1.cid, pv, time_len
from t_avg_score t1 join t_pv t2 on t1.cid=t2.cid
order by pv desc, time_len desc
limit 3