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

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