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

SELECT 
	MONTH month, 
	ranking,
	song_name,
	pv AS play_pv 
FROM
	(
	SELECT 
		MONTH as month,
		YEAR,
		ROW_NUMBER() over (
			PARTITION BY
			MONTH,
		YEAR 
		ORDER BY
			pv DESC,
			song_id 
		) AS ranking,
		song_name,
		pv 
	FROM
		(
		SELECT
			song_id,
			MONTH,
			YEAR,
			song_name,
			count(*) pv 
		FROM
			(
			SELECT
				log.song_id,
				info.age,
				log.fdate,
				song.singer_name,
				song.song_name,
				MONTH ( log.fdate ) AS MONTH,
				YEAR ( log.fdate ) AS YEAR 
			FROM
				play_log log
				INNER JOIN user_info info ON info.user_id = log.user_id
				INNER JOIN song_info song ON song.song_id = log.song_id 
			WHERE
				info.age BETWEEN 18 
				AND 25 
				AND song.singer_name = '周杰伦' 
			) a 
		WHERE
			YEAR = 2022 
		GROUP BY
			song_id,
			MONTH,
			YEAR,
			song_name 
		) c 
	) z 
WHERE
	ranking <=3