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

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