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

WITH temp AS (
    -- 第一步：过滤出 2022年 + 周杰伦 + 18到25岁的用户记录
    SELECT 
        MONTH(pl.fdate) AS play_month,
        si.song_name,
        si.song_id 
    FROM play_log pl
    JOIN song_info si 
        ON pl.song_id = si.song_id
    JOIN user_info ui 
        ON pl.user_id = ui.user_id
    WHERE YEAR(pl.fdate) = 2022 
      AND si.singer_name = '周杰伦'
      AND ui.age BETWEEN 18 AND 25
),

temp1 AS (
    -- 第二步：只按“月份”和“歌曲”分组，计算总播放量
    SELECT 
        play_month AS month,
        song_name,
        COUNT(*) AS play_pv,
        -- 如果播放量相同，通常按 song_id 升序做唯一排序，保证结果稳定
        ROW_NUMBER() OVER(PARTITION BY play_month ORDER BY COUNT(*) DESC, song_id ASC) AS rnk
    FROM temp
    GROUP BY 
        play_month,
        song_name,
        song_id
)

-- 第三步：输出每个月的前三名
SELECT 
    month,
    rnk AS ranking,
    song_name,
    play_pv
FROM temp1
WHERE rnk <= 3
ORDER BY 
    month ASC, 
    ranking ASC;