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

 WITH t AS( 
    SELECT DISTINCT month,song_name,song_id,COUNT(age) OVER (PARTITION BY month,song_name) AS play_pv
    FROM(    
        SELECT month(a.fdate) AS month,b.age,c.song_name,c.singer_name,a.song_id
        FROM play_log AS a
        JOIN user_info AS b ON a.user_id = b.user_id
        JOIN song_info AS c ON a.song_id = c.song_id
    ) AS d
    WHERE age >= 18 AND age <= 25 AND singer_name = '周杰伦'
 )

 SELECT * 
 FROM(   
    SELECT month,ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC,song_id ASC) AS ranking,
    song_name,play_pv
    FROM t
 ) AS e
 WHERE ranking <= 3
 ORDER BY month ASC,ranking ASC

小白写的可以看看