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

#从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲

#18-25岁 2022年 每个月 播放次数tops 周杰伦

with all_info as
(
    select
p.fdate
,p.user_id as user_id
,p.song_id as song_id
,u.age
,s.song_name
,s.singer_name
,month(fdate) as fdate_mon
from play_log p 
left join user_info u
on p.user_id = u.user_id
left join song_info s
on p.song_id = s.song_id
where p.fdate between "2022-01-01" and "2022-12-31"  and u.age between 18 and 25
and singer_name = '周杰伦'
)
,count_info as
(
select
*
,count(user_id)over(partition by fdate_mon,song_id) as cnt
from all_info 
)
,rank_info as
(
select
 fdate_mon
,song_name
,song_id
,cnt
,row_number()over(partition by fdate_mon order by cnt desc) as t_rank
from (select distinct fdate_mon ,song_name, song_id, cnt from count_info) as a1
)

select
fdate_mon as month
#这里date_format(fdate_month,'%m')不行
,t_rank as ranking
,song_name
,cnt as play_pv
from rank_info
where t_rank in(1,2,3)
order by  month asc, ranking asc, song_id asc