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

select month, ranking, song_name, play_pv
from (
    select 
        month(fdate) as month,
        s.song_name,
        count(p.song_id) as play_pv,
        row_number() over (PARTITION BY month(fdate) ORDER BY count(p.song_id) DESC, p.song_id) as ranking
    from play_log p
    inner join user_info u on p.user_id = u.user_id
    inner join song_info s on p.song_id = s.song_id
    where 18 <= u.age and u.age <= 25
    and s.singer_name = '周杰伦'
    group by month(fdate), s.song_id, s.song_name
) as ranked_songs
where ranking <= 3
order by month, ranking;