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

with monthly_song_pv as (

        SELECT month(pl.fdate) as month,si.song_name,count(*) as play_pv

        FROM play_log as pl

        inner join song_info as si on pl.song_id=si.song_id AND si.singer_name='周杰伦'

        inner join user_info as ui on pl.user_id=ui.user_id AND ui.age BETWEEN 18 and 25

        group by month(pl.fdate),si.song_name)

SELECT month,ranking,song_name,play_pv

FROM

    (SELECT month,song_name,play_pv,

    ROW_NUMBER() over(partition by month order by play_pv desc) as ranking

    from monthly_song_pv) as w1

WHERE ranking <=3

order by month,ranking;