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

with tiaojian as (
select 
month(fdate) as month,
dense_rank()over(partition by month(fdate) order by count(p.song_id) desc,p.song_id asc) as pn,
song_name,
count(p.song_id) cnt
from play_log p left join song_info sf 
on p.song_id=sf.song_id
left join user_info u on u.user_id=p.user_id
where
singer_name="周杰伦"
and 
age between 18 and 25
group by month(fdate),p.song_id,song_name
)


select 
month,
pn as ranking,
song_name,
cnt as play_pv
from tiaojian
where
pn<=3