题解 | 最受欢迎的top3课程

    (
        select
            p.cid,
            round(timestampdiff(minute, start_time, end_time), 3) as duration,
            score
        from
            play_record_tb p
            join course_info_tb c on p.cid = c.cid
        where
            timestampdiff(
                day,
                c.release_date,
                date_format(p.start_time, '%Y-%m-%d')
            ) <= 7
    ) a

这个方法用到子查询，题目的要求是1️⃣一周内，2️⃣平均分>3，3️⃣观看量，4️⃣观看时长。

首先我们可以在两个表合并后，用where筛选出start_time - release_time <= 7 的样本，然后计算得到观看时长，这里也要获得分数，因为题目要求中会用到score。

select
    a.cid,
    count(*) as pv,
    sum(duration) as time_len
from
    (
        select
            p.cid,
            round(timestampdiff(minute, start_time, end_time), 3) as duration,
            score
        from
            play_record_tb p
            join course_info_tb c on p.cid = c.cid
        where
            timestampdiff(
                day,
                c.release_date,
                date_format(p.start_time, '%Y-%m-%d')
            ) <= 7
    ) a
group by
    a.cid
having
    avg(score) >= 3
order by
    pv desc,
    time_len desc
limit
    3

根据uid进行group by，可以得到一些聚合的信息，count(*)是每个课的观看次数，还有总共的观看时长求和，having可以用于筛选group by完之后的条件，比如平均分 >= 3，再排序和返回3个就行。