# 用户年龄-18-25岁； year = 2022; 
# 筛选歌手名称是周杰伦的歌曲ID
# group by month; 每一组都根据ROW_number降序排序；count算歌曲的数量

# 1，给三个表重命名：
# 听歌流水表 play_log p    p.fdate, p.user_id, p.song_id
# 歌曲信息表 song_info s    s.song_id, s.song_name, s.singer_name
# 用户表user_info  u    u.user_id, u.age

# 2, 建立筛选条件：
# where (u.age between 18 and 25) and (year(p.fdate) = 2022) and (s.singer_name = "周杰伦")

# 3, 给三个表合并

# from play_log p join song_info s on p.song_id = s.song_id join user_info u on p.user_id = u.user_id

# 4, 三个表合并后，根据筛选条件，按照月份，和歌曲ID分组
with new_table as(
    select month(p.fdate) as month,
       s.song_id as song_id,
       s.song_name as song_name
from play_log p 
left join song_info s on p.song_id = s.song_id 
left join user_info u on p.user_id = u.user_id
where (u.age between 18 and 25 ) and (year(p.fdate) = 2022) and (s.singer_name ="周杰伦")
),

# 5, 现在形成了一个新表，第一列是月份，第二列是歌曲ID，第三列是歌曲名。需要做的是，根据月份和歌曲ID分组，并且可以算出来每一组别下的count，即每个月每首歌的count

ranked_table as(
select month,
       row_number () over (partition by month order by count(song_id) desc, song_id) as ranking, # 这里有两个条件，因为可能会出现一个月，有两首歌的播放次数相同的情况，所以利用歌曲数量降序，和歌曲ID来排序
       song_name,
       count(*) as play_pv
from new_table
group by month, song_id, song_name # 必须得月份，名字，ID都要，不然拍不出来
)

# 最后一步，筛选ranking小于等于3的，因为含有窗口函数，所以不能用where和
select month, ranking, song_name, play_pv
from ranked_table
where ranking <= 3
order by month, ranking