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

select 
    month,
    ranking,
    song_name,
    play_pv 
from (
    select month, song_name, play_pv, song_id,
    row_number() over(partition by month order by play_pv desc, song_id) as ranking 
    from (
        select 
            month(fdate) as month,
            song_id,
            song_name,
            count(*) as play_pv
        from play_log 
        join song_info using(song_id)
        join user_info using(user_id)
        where year(fdate) = 2022
            and age between 18 and 25
            and singer_name = '周杰伦'
        group by month(fdate), song_id, song_name
    ) a 
) b 
where ranking <= 3
order by month, ranking;