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

WITH t1 AS(
    SELECT
        MONTH(pl.fdate) AS month,
        pl.song_id,
        COUNT(pl.user_id) AS play_times
    FROM
        play_log pl
        LEFT JOIN user_info ui ON pl.user_id = ui.user_id
        LEFT JOIN song_info si ON pl.song_id = si.song_id
    WHERE
        YEAR(pl.fdate) = 2022
        AND (ui.age BETWEEN 18 AND 25)
        AND si.singer_name = '周杰伦'
    GROUP BY
        MONTH(pl.fdate),
        pl.song_id
),
t2 AS(
    SELECT
        *
    FROM(
        SELECT
            month,
            song_id,
            play_times,
            RANK() OVER(
                PARTITION BY month
                ORDER BY play_times DESC, song_id ASC
            ) AS ranking
        FROM
            t1) tmp
    WHERE
        ranking <= 3
)
SELECT
    t2.month,
    t2.ranking,
    si.song_name,
    t2.play_times AS play_pv    
FROM
    t2 LEFT JOIN song_info si USING(song_id)
ORDER BY
    t2.month ASC, t2.ranking ASC, si.song_id ASC;