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

select month,ranking,song_name,play_pv
from(
select month,
row_number()over(partition by month order by play_pv desc,songid asc) as ranking,
song_name,play_pv
from
(
select month,songid,songname as song_name,count(*) as play_pv from
(
select month(table1.fdate) as month, u.user_id, songid,table1.song_name as songname,table1.singer_name,age from user_info as u
join
(
select fdate, user_id, s.song_id as songid,song_name,singer_name from play_log as p
join 
(
select song_id, song_name,singer_name from song_info
) as s
on p.song_id = s.song_id
where date_format(fdate,'%Y') = 2022 and singer_name = '周杰伦'
) as table1
on u.user_id = table1.user_id
where age between 18 and 25) as table2
group by month,songname,songid
) as table3
) as table4
where ranking between 1 and 3