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

select * from (select
month,
row_number() over(partition by month order by play_pv desc,song_id asc) as ranking,
song_name,
play_pv
from
(with tmp as (select 
month(fdate) as month,
t1.user_id,
t1.song_id,
t2.song_name,
t2.singer_name,
t3.age
 from play_log t1
left join song_info t2
on t1.song_id =t2.song_id
left join user_info t3
on t1.user_id =t3.user_id
where t3.age between 18 and 25
and t2.singer_name ="周杰伦")
select month,song_id,song_name,count(song_name) play_pv from tmp 
group by month,song_id,song_name) a) b
where ranking <=3;

这个题song_id也要加入排序，要不就会有用例不通过的情况