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

select
month,
ranking,
song_name,
play_pv
from
(select
month(P.fdate) as month,
ROW_NUMBER() OVER(PARTITION BY month(P.fdate) ORDER BY COUNT(*) DESC,P.song_id) as ranking,
S.song_name,
COUNT(*) as play_pv
from
play_log as P 
left join song_info as S on P.song_id = S.song_id
left join user_info as U on P.user_id = U.user_id
where 
year(P.fdate) = 2022
and S.singer_name = '周杰伦'
AND S.song_name IS NOT NULL
and U.age between 18 and 25
group by
month(P.fdate),S.song_name,P.song_id) as R 
where 
R.ranking  <= 3
order by
month, ranking;