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

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