题解 | 最受欢迎的top3课程

with t1 as(
select cid
,score
,start_time
,round(timestampdiff(minute,start_time,end_time),3) len
from play_record_tb
)
,t2 as(
select cid
,avg(score) avg_score
from play_record_tb
group by cid
)
select t1.cid cid
,count(*) pv
,sum(len) time_len
from t1 join t2 on t1.cid=t2.cid
join course_info_tb t3 on t1.cid=t3.cid
where avg_score>=3 and start_time<=date_add(release_date,interval 7 day)
group by t1.cid
order by pv desc,time_len desc
limit 3;

服啦看了半天居然是这个问题