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

# 18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲。
# 1、先写一个临时表用于存储18-25岁的用户在2022年每个月播放的每个音乐id的次数
with cishu AS
(
select month(t1.fdate) as month,t1.song_id,count(song_id) as play_pv
from 
play_log as t1
inner join
user_info as t2 on t1.user_id=t2.user_id
where t2.age between '18' and '25'
group by month(t1.fdate),t1.song_id
)

select a.month,a.rnk as ranking,a.song_name,a.play_pv
from 
(select a1.month,a1.song_id,a2.song_name,a1.play_pv,
row_number()over(partition by a1.month order by a1.play_pv desc) as rnk
from cishu as a1
left outer join
song_info as a2 on a1.song_id=a2.song_id
where a2.singer_name='周杰伦'
) as a
where a.rnk<=3