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

with user as (
select user_id
from user_info where age between 18 and 25
),
play as (

select 
month(fdate) mon,
song_id,
count(fdate) cnt
 from play_log
join user on play_log.user_id=user.user_id
group by month(fdate),song_id
),
ran as (
select 
mon,
play.song_id,
song_name,
cnt,
row_number()over(partition by mon order by cnt desc) as rands
from play 
join song_info
on play.song_id=song_info.song_id
where song_info.singer_name='周杰伦'
)
select 
mon as month,
rands as ranking,
song_name,
cnt as play_pv

 from ran 
 where rands<=3