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

select b.month, b.ranking,  song_name, b.play_pv
from (
select a.song_id, month(a.fdate)month, row_number()over(partition by month(a.fdate) order by count(a.song_id) desc,a.song_id)ranking,a.song_name , count(a.song_name)play_pv
from(
select s.song_id,p.fdate,s.song_name, dense_rank()over(partition by month(p.fdate) order by s.song_name )dr from play_log p  inner join song_info s on p.song_id=s.song_id inner join user_info u on p.user_id=u.user_id where s.singer_name='周杰伦' and u.age between 18 and 25)a
group by month(a.fdate),a.song_name,a.song_id
order by month(a.fdate),play_pv desc) b
where b.ranking<=3 
order by b.month, b.play_pv desc,b.song_id,b.ranking

这个坑还是挺大的，你发现你自测运行成功之后提交并没有成功，因为他还有一小步，是要按照song_id来排序的，这个千万要注意啊