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

# select user_id from user_info where age between 18 and 25

# select fdate,user_id,song_id from play_log where year(fdate) = 2022

# select song_id,song_name from song_info where singer_name = '周杰伦'



with t1 as(
select 
    month(a.fdate) month,b.song_id,b.song_name,count(1) play_pv
from 
(select fdate,user_id,song_id from play_log where year(fdate) = 2022) a
join (select song_id,song_name from song_info where singer_name = '周杰伦') b on a.song_id = b.song_id
join (select user_id from user_info where age between 18 and 25) c on a.user_id = c.user_id
group by month(a.fdate),b.song_id,b.song_name
),

t2 as(
select 
    t1.month,
    row_number() over(partition by t1.month order by t1.play_pv desc,t1.song_id) ranking,
    t1.song_id,
    t1.song_name,
    t1.play_pv
from t1 )

select 
    t2.month,
    t2.ranking,
    t2.song_name,
    t2.play_pv
from t2 where t2.ranking <=3
order by t2.month,t2.ranking;