题解 | 每个月Top3的周杰伦歌曲
select
c.month,
c.ranking,
s.song_name,
c.pv play_pv
from
(
select
month(fdate) month,
p.song_id,
row_number() over (
partition by
month (fdate)
order by
count(*) desc,s.song_id
) ranking,
count(*) pv
from
play_log p
left join song_info s on s.song_id = p.song_id
join user_info u on p.user_id = u.user_id
where
s.singer_name = '周杰伦'
and u.age between 18 and 25
group by
month(fdate),p.song_id
) c
left join song_info s on s.song_id=c.song_id
where c.ranking<=3
