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

WITH 
age AS (
    SELECT 
        a.fdate, 
        a.user_id, 
        a.song_id,  -- 只保留一份 song_id
        b.age
    FROM play_log a 
    LEFT JOIN user_info b
        ON a.user_id = b.user_id
    WHERE b.age BETWEEN 18 AND 25
),
song AS (
    -- 👇 这里必须明确写字段，不能用 *
    SELECT 
        c.fdate,
        c.user_id,
        c.song_id,  -- 只保留一份，避免重复
        c.age,
        d.song_name,
        d.singer_name
    FROM age c
    LEFT JOIN song_info d
        ON c.song_id = d.song_id
    WHERE d.singer_name = '周杰伦'
),
temp AS (
    SELECT 
        song_id,
        MONTH(fdate) AS month,
        song_name,
        COUNT(song_name) AS play_pv
    FROM song
    GROUP BY month(fdate), song_id, song_name
),
final as (
    SELECT 
        month,
        ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC, song_id ASC) AS ranking,
        song_name,
        play_pv
    FROM temp
    ORDER BY month ASC, play_pv DESC, song_id ASC)
SELECT *
from final
where ranking <=3
order by month, ranking;