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

-- 逻辑拆解：筛选条件-18-25岁，2022年，播放次数top3，周杰伦；分组对象：每个月
-- 思路就是首先将所有符合条件的内容筛选出来（按照逻辑拆解去做），并且求出播放次数+按照播放次数去排名，最后取前三名
WITH table_1 AS(
    SELECT 
        MONTH(t2.fdate) AS month,
        COUNT(t2.song_id) play_pv,
        t3.song_name,
        ROW_NUMBER() OVER(partition by MONTH(t2.fdate) order by COUNT(t2.song_id) DESC) ranking
    FROM user_info t1
    INNER JOIN play_log t2
    ON t1.user_id = t2.user_id
    INNER JOIN song_info t3
    ON t2.song_id = t3.song_id
    WHERE t1.age BETWEEN '18' AND '25'
    AND t2.fdate BETWEEN '2022-01-01' AND '2022-12-31'
    AND t3.singer_name = '周杰伦'
    GROUP BY MONTH(t2.fdate),t3.song_name
)
SELECT 
    t1.month,
    t1.ranking,
    t1.song_name,
    t1.play_pv
FROM table_1 t1
WHERE t1.ranking <= 3