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

-- 定义CTE临时表t1：筛选18-25岁用户在2022年播放周杰伦歌曲的记录
WITH t1 AS (
    SELECT 
        play_log.fdate fdate,
        play_log.user_id user_id,
        song_info.song_name singer_name,
        song_info.song_id song_id
    FROM play_log
    JOIN song_info ON play_log.song_id = song_info.song_id
    JOIN user_info ON play_log.user_id = user_info.user_id
    WHERE 
        (user_info.age BETWEEN 18 AND 25)
        AND song_info.singer_name = '周杰伦'
        AND YEAR(fdate) = 2022
),

-- 定义CTE临时表t2：按年月分组统计播放量并排名
t2 AS (
    SELECT 
        DATE_FORMAT(fdate, '%y-%m') fddate,
        singer_name,
        COUNT(*) play_pv,
        song_id,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_FORMAT(fdate, '%y-%m')
            ORDER BY COUNT(*) DESC, song_id
        ) rk
    FROM t1
    GROUP BY fddate, singer_name, song_id
)

-- 最终查询：提取每月前3名歌曲的播放数据
SELECT 
    CAST(RIGHT(fddate, 2) AS SIGNED) AS month,
    rk ranking,
    singer_name song_name,
    play_pv
FROM t2
WHERE rk <= 3;