题解 | 最受欢迎的top3课程

SELECT a.cid,a.play_cnt pv,a.time_len
FROM(
    SELECT
        t1.cid,
        COUNT(*) play_cnt,
        SUM(timestampdiff(minute,t2.start_time,t2.end_time)) time_len,
        DENSE_RANK() OVER(order by COUNT(*) DESC,SUM(timestampdiff(minute,t2.start_time,t2.end_time)) DESC) ranking
    FROM course_info_tb t1
    INNER JOIN play_record_tb t2
    ON t1.cid = t2.cid
    AND t2.start_time IS NOT NULL
    AND t2.end_time IS NOT NULL
    AND timestampdiff(day,t1.release_date,date_format(t2.start_time,'%Y-%m-%d')) <= 7
    GROUP BY t1.cid
    HAVING AVG(t2.score) >= 3
) a
WHERE a.ranking <= 3