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

select * from 
(select t1.month,row_number() over(partition by t1.month order by t1.play_pv desc,t1.song_id) ranking,t1.song_name,t1.play_pv from
    (SELECT 
        month(p.fdate) month,s.song_name song_name,count(*) play_pv,s.song_id song_id
    FROM 
        play_log p JOIN song_info s ON p.song_id = s.song_id JOIN user_info u ON p.user_id = u.user_id
    WHERE 
        u.age BETWEEN 18 AND 25
        AND s.singer_name = '周杰伦'
        AND YEAR(p.fdate) = 2022
    group by
        month(p.fdate),s.song_id,s.song_name
        ) t1
) t2 where t2.ranking <= 3;