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

select *
from (
select  month,
        row_number()over(partition by month order by play_pv desc,song_id) as ranking,
        song_name,
        play_pv
from (
select  month(pl.fdate) as month,
        si.song_name,
        si.song_id,
        count(1) as play_pv
from play_log pl 
left join user_info ui on pl.user_id=ui.user_id
left join song_info si on pl.song_id=si.song_id 
where ui.age between 18 and 25
and si.singer_name='周杰伦'
group by month(pl.fdate),
        si.song_id,
        si.song_name
) t
) t2
where t2.ranking<=3
order by month,ranking