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

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