题解 | 最受欢迎的top3课程

 -- top3视频、 平均评分不低于3分、pv 倒序、播放时长(一周内)
WITH prt AS (
    SELECT
        id,
        uid,
        cid,
        start_time,
        end_time,
        TIMESTAMPDIFF(minute, start_time, end_time) AS per_time,
        score
    FROM play_record_tb
)
SELECT
    cid,
    count(*) AS pv,
    ROUND(sum(per_time), 3) AS time_len
FROM prt 
JOIN course_info_tb USING (cid)
WHERE DATEDIFF(start_time, release_date) < 7
GROUP BY cid
HAVING AVG(score) >= 3
ORDER BY pv DESC,time_len DESC
LIMIT 3;