题解 | 每个月Top3的周杰伦歌曲
每个月Top3的周杰伦歌曲
https://www.nowcoder.com/practice/4ab6d198ea8447fe9b6a1cad1f671503
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 month(pl.fdate) as month,
si.song_name,
si.song_id,
count(1) as play_pv
from play_log pl
left join user_info ui on pl.user_id=ui.user_id
left join song_info si on pl.song_id=si.song_id
where ui.age between 18 and 25
and si.singer_name='周杰伦'
group by month(pl.fdate),
si.song_id,
si.song_name
) t
) t2
where t2.ranking<=3
order by month,ranking
SHEIN希音公司福利 370人发布