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

WITH
    month_play AS (
        select
            MONTH (fdate) AS month,
            P.song_id AS id,
            S.song_name AS name,
            count(*) AS cnt
        from
            play_log P
            JOIN user_info U ON P.user_id = U.user_id
            JOIN song_info S ON P.song_id = S.song_id
        WHERE
            U.age BETWEEN 18 AND 25
            AND S.singer_name = '周杰伦'
        group by
            MONTH(fdate),
            P.song_id,
            S.song_name
    ),

ranked_song AS (
    SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY month
                        ORDER BY cnt DESC, id ) AS rnk
    FROM month_play
)

SELECT month, rnk AS ranking, name AS song_name, cnt AS play_pv
FROM ranked_song
WHERE rnk <=3;