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

select
    month,
    ranking,
    song_name,
    play_pv
from
    (
        select
            month(pl.fdate) as month,
            pl.song_id,
            si.song_name,
            row_number() over (
                PARTITION by
                    month(pl.fdate)
                order by
                    count(pl.song_id)desc,pl.song_id
            ) as ranking,
            count(pl.song_id) as play_pv
        from
            play_log as pl
            inner join user_info as ui on pl.user_id = ui.user_id
            inner join song_info as si on pl.song_id = si.song_id
        where
            ui.age between 18 and 25
            and year (pl.fdate) = 2022
            and si.singer_name = "周杰伦"
        group by
            month (pl.fdate),
            pl.song_id,
            si.song_name
    ) as tmp
where
    ranking < 4
order by
    month,
    ranking asc