题解 | 每个月Top3的周杰伦歌曲
每个月Top3的周杰伦歌曲
https://www.nowcoder.com/practice/4ab6d198ea8447fe9b6a1cad1f671503
with t as(select
month(fdate) as month,
b.song_name,
b.song_id
from play_log a
join song_info b on a.song_id=b.song_id and b.singer_name='周杰伦'
join user_info c on a.user_id=c.user_id and c.age between 18 and 25)
select*
from(
select
month,
row_number()over(partition by month order by play_pv desc,song_id) as ranking ,
song_name,
play_pv
from(
select distinct
song_id,
month,
song_name,
count(song_name) as play_pv
from t
group by month,song_name,song_id
order by month,play_pv desc
) t1
)t2
where ranking<=3