题解 | 每个月Top3的周杰伦歌曲

WITH song_pv_ranked AS (
    SELECT
        MONTH(p.fdate) AS month,
        ROW_NUMBER() OVER (PARTITION BY MONTH(p.fdate) ORDER BY COUNT(*) DESC,s.song_id ASC) AS ranking,
        s.song_name,
        COUNT(*) as play_pv
    FROM
        play_log AS p
        INNER JOIN
        song_info AS s ON p.song_id=s.song_id
            INNER JOIN user_info AS u ON p.user_id=u.user_id
    WHERE
        s.singer_name = '周杰伦'
        AND u.age BETWEEN 18 AND 25
        AND YEAR(p.fdate) = 2022
    GROUP BY
        MONTH(p.fdate),
        s.song_id,
        s.song_name
)
SELECT
    *
FROM
    song_pv_ranked
WHERE
    ranking<=3
ORDER BY
    month,
    ranking;