题解 | 最受欢迎的top3课程

WITH general AS (
    SELECT p.cid, p.score,
        TIMESTAMPDIFF(minute, p.start_time, p.end_time) AS time_len,
        c.duration
    FROM play_record_tb AS p
    LEFT JOIN course_info_tb AS c
    ON p.cid = c.cid
    WHERE DATEDIFF(DATE(p.start_time), c.release_date) <= 7

)
SELECT cid, COUNT(*) AS pv, SUM(time_len) AS time_len
FROM general
GROUP BY cid
HAVING AVG(score) >= 3
ORDER BY pv DESC, time_len DESC
LIMIT 3;