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

WITH tongji AS 
(
    SELECT 
        b.song_id,
        a.user_id ,
        MONTH(b.fdate) AS month,
        c.song_name 
    FROM user_info as a
    LEFT JOIN play_log as b
    ON a.user_id = b.user_id
    LEFT jOIN song_info as c
    ON b.song_id = c.song_id
    WHERE a.age BETWEEN 18 AND 25
    AND c.singer_name = "周杰伦"
) 
,tongji_2 AS
(
    SELECT 
        month,
        song_id,
        song_name,
        COUNT(*) AS play_pv
    FROM tongji
    GROUP BY month,song_name,song_id
)
,tongji_3 AS 
(
    SELECT 
        month,
        ROW_NUMBER() OVER(PARTITION BY month ORDER BY play_pv DESC,song_id ) AS ranking,
        song_name,
        play_pv
    FROM tongji_2
)
SELECT * FROM tongji_3 WHERE ranking <=3

## 我的思路就是通过cte结构来完成每一步的拆解，首先是合并表格并且进行年龄和歌手筛选
第二步是进行分组聚合，通过各月份不同歌曲的听歌次数
第三部是进行窗口函数打标签来进行排序（注意，相同次数的歌曲需要进行按song_id进行升序排序）
第四步就是进行筛选排序前三的