题解 | 每个月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 (a.fdate) as month,
    b.song_name,
    b.song_id,
    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,song_id)a ) a where ranking <= 3