题解 | 最受欢迎的top3课程

select distinct  cid,pv,round(sum(time_len) over (partition by cid),3) as time_len
from (
    select  play_record_tb.cid,count(* ) over (partition by course_info_tb.cid)as pv
         ,round(timestampdiff(minute ,start_time,end_time),3) as time_len
        , avg(score) over (partition by course_info_tb.cid ) as score
            from course_info_tb join play_record_tb on
        course_info_tb.cid=play_record_tb.cid
        where timestampdiff(day ,release_date,start_time)<=7
            order by score desc
     ) as t
where score >=3
order by pv desc
limit 3