题解 | 每个月Top3的周杰伦歌曲
每个月Top3的周杰伦歌曲
https://www.nowcoder.com/practice/4ab6d198ea8447fe9b6a1cad1f671503
with play_log as (
select month(fdate) as month,
p.song_id,
song_name,
count(*) as play_pv
from play_log p join user_info u on p.user_id = u.user_id
join song_info s on p.song_id = s.song_id
where YEAR(fdate) = 2022 and singer_name = '周杰伦' and age between 18 and 25
group by month(fdate),song_id,song_name),
rankedlist as (
select month,
row_number() over (partition by month order by play_pv desc,song_id) ranking,
song_name,
play_pv
from play_log
)
select month,
ranking,
song_name,
play_pv
from rankedlist
where ranking <= 3
投递腾讯等公司6个岗位 >
