题解 | 最受欢迎的top3课程
最受欢迎的top3课程
https://www.nowcoder.com/practice/b9b33659559c46099aa3257da0374a48
SELECT
prt.cid,
COUNT(DISTINCT prt.id) pv,
SUM(TIMESTAMPDIFF(MINUTE, prt.start_time, prt.end_time)) time_len
FROM play_record_tb prt
JOIN course_info_tb cit ON cit.cid = prt.cid
WHERE TIMESTAMPDIFF(DAY, cit.release_date, start_time) < 7
GROUP BY prt.cid
HAVING AVG(score) >= 3
ORDER BY pv DESC, time_len DESC
LIMIT 3
