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

select
month
,rn as ranking
,song_name
,play_pv
from(
select 
month(fdate) as month
,a.song_id as  song_id
,b.song_name as song_name
,count(1) as play_pv
,row_number() over(partition by month(fdate) order by count(a.song_id) desc,b.song_id) as rn
from play_log a
left join song_info b
on a.song_id = b.song_id
left join user_info c
on a.user_id = c.user_id
where left(a.fdate,4) = '2022'
and c.age  between 18 and 25
and b.singer_name = '周杰伦'
group by 1,2,3) as tmp
where rn<=3
order by month, ranking;