题解 | 最受欢迎的top3课程

SELECT p.cid,
       COUNT(uid) AS pv,
       SUM(TIMESTAMPDIFF(MINUTE,start_time,end_time)) AS time_len
FROM play_record_tb p
LEFT JOIN course_info_tb c
ON p.cid=c.cid 
WHERE p.cid IN (
    SELECT cid
    FROM play_record_tb 
    GROUP BY cid
    HAVING AVG(score)>=3
) AND TIMESTAMPDIFF(DAY,release_date,DATE(start_time))<=7
GROUP BY p.cid
ORDER BY pv DESC,time_len DESC
LIMIT 3