题解 | 最受欢迎的top3课程

/*
#先计算平均评分、一周内的观看次数、一周内的播放总时长
with tb1 as
(
    select
        p.cid,
        avg(p.score) as avg_score,
        count(case when datediff(date(p.start_time), c.release_date)<=7 then 1 end) as counts,
        sum(case when datediff(date(p.start_time), c.release_date)<=7 then timestampdiff(minute,p.start_time,p.end_time) else 0 end) as sumtime
    from play_record_tb p
    join course_info_tb c on p.cid = c.cid
    group by cid
),
#给它们排序
tb2 as
(
    select 
        cid,
        avg_score,
        counts,
        sumtime,
        rank() over(order by counts desc,sumtime desc) as rk
    from tb1
    where avg_score>=3
)
#最终输出
select
    cid,
    counts as pv,
    sumtime as time_len
from tb2
where rk<=3
order by pv desc,time_len desc;
*/

#走一下官方的解题
select 
    p.cid,
    count(*) as pv,
    sum(timestampdiff(minute,p.start_time,p.end_time)) as time_len
from course_info_tb c
join play_record_tb p on c.cid=p.cid
where timestampdiff(day, c.release_date, date_format(p.start_time, '%Y-%m-%d')) <= 7
group by p.cid
    having avg(p.score) >=3
order by pv desc,time_len desc
limit 3;

笨鸟笨鸟，干不过官方思路，我的代码咋这么长，唉😔