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

WITH t1 AS (
    SELECT MONTH(fdate) AS month,
           a.user_id,
           b.song_id,
           b.song_name
    FROM play_log a
    LEFT JOIN song_info b
    ON a.song_id = b.song_id
    LEFT JOIN user_info c
    ON a.user_id = c.user_id
    WHERE SUBSTR(fdate,1,4)='2022'
    AND b.singer_name = '周杰伦'
    AND c.age BETWEEN 18 AND 25
),
t2 AS (
    SELECT
        month,
        song_id,
        song_name,
        count(song_name) AS play_pv
    FROM t1
    GROUP BY month, song_id, song_name
),
t3 AS (
    SELECT month,
           ROW_NUMBER()
               over (PARTITION BY month
                   ORDER BY play_pv DESC,song_id ASC) AS ranking,
           song_name,
           song_id,
           play_pv
    FROM t2
)
SELECT month,
       ranking,
       song_name,
       play_pv
FROM t3
WHERE ranking <= 3;

# 这道题的细节在于排名需要根据歌曲编号升序排序！