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

select
    nt.month,
    nt.ranking,
    nt.song_name,
    nt.play_pv
from
    (
        select
            t.month,
            row_number() over (
                partition by
                    t.month
                order by
                    play_pv desc
            ) ranking,
            si.song_name,
            t.play_pv
        from
            (
                select
                    month (pl.fdate) month,
                    pl.song_id,
                    count(pl.user_id) play_pv
                from
                    play_log pl
                    join user_info ui on ui.user_id = pl.user_id
                where
                    ui.age between 18 and 25
                    and year (pl.fdate) = 2022
                group by
                    month (pl.fdate),
                    pl.song_id
            ) t
            join song_info si on t.song_id = si.song_id
        where
            si.singer_name = '周杰伦'
    ) nt
where
    nt.ranking <= 3