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

with s as (
select play_log.fdate as fdate, play_log.user_id as user_id, play_log.song_id as song_id,song_info.song_name as song_name, song_info.singer_name as singer_name, user_info.age as age,
row_number() over (order by play_log.fdate) as xulie
from play_log
left join song_info on play_log.song_id = song_info.song_id
left join user_info on play_log.user_id = user_info.user_id
),

ss as (select
fdate, song_id, song_name, xulie,
SUBSTRING(CAST(fdate AS CHAR), 6, 2) AS month_part,
       CASE
           WHEN SUBSTRING(CAST(fdate AS CHAR), 6, 1) = '0' THEN SUBSTRING(CAST(fdate AS CHAR), 7, 1)
           ELSE SUBSTRING(CAST(fdate AS CHAR), 6, 2)
       END AS month
from s
where singer_name = '周杰伦' and age between 18 and 25 
and year(fdate) = 2022
),

sss as(
select month,song_name,
count(xulie) as play_pv,song_id,month_part
from ss
group by month,song_name,song_id,month_part
order by song_name),

ssss as (select  month,song_name,play_pv,song_id,month_part
from sss
ORDER BY play_pv desc),

sssss as (select month,ROW_NUMBER() over (partition by month order by play_pv desc, song_id asc) as ranking, song_name, play_pv,month_part
from ssss
order by month_part)

select month,ranking,song_name,play_pv
from sssss
where ranking in (1,2,3)
order by month_part,ranking
;