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

select
    month,
    ranking,
    song_name,
    play_pv
from (
    select 
        *,
        row_number() over (partition by month order by play_pv desc,song_id asc) ranking
    from (
        select
            month(p.fdate) as month,
            s.song_name,
            s.song_id,
            count(p.song_id) as play_pv
        from play_log p 
        left join user_info u
        on p.user_id = u.user_id
        left join song_info s 
        on p.song_id = s.song_id
        where u.age between 18 and 25
        and s.singer_name = '周杰伦'
        and year(p.fdate) = 2022
        group by 1,2,3
    ) x1
) x2
where ranking <= 3