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

WITH
t1 AS (
SELECT      -- 清洗数据，获取2022年18~25岁用户每个月每首周杰伦歌曲的播放次数
    MONTH(a.fdate) AS month,
    b.song_id,
    b.song_name,
    COUNT(*) AS play_pv
FROM play_log AS a
INNER JOIN song_info AS b
    ON a.song_id = b.song_id
    AND b.singer_name = '周杰伦'
INNER JOIN user_info AS c
    ON a.user_id = c.user_id
    AND c.age >= 18 AND c.age <= 25
WHERE a.fdate >= '2022-01-01' AND a.fdate < '2023-01-01'
GROUP BY MONTH(a.fdate),b.song_name,b.song_id
),

t2 AS (
SELECT      -- 用窗口函数对每个月每首歌的播放次数进行降序
    month,
    RANK() OVER(PARTITION BY month ORDER BY play_pv DESC,song_id) AS ranking,
    song_name,
    play_pv
FROM t1
)

SELECT      -- 主查询，只显示每个月播放次数top 3的歌曲信息
    month,
    ranking,song_name,play_pv
FROM t2
WHERE ranking <= 3
ORDER BY month,ranking,song_name