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

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