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

WITH  
    a AS (  
        SELECT  
            month(fdate) AS month,  
            t1.user_id,  
            t1.song_id,  
            t2.song_name,  
            t2.singer_name,  
            t3.age  
        FROM  
            play_log t1  
        INNER JOIN song_info t2 ON t1.song_id = t2.song_id  
        INNER JOIN user_info t3 ON t1.user_id = t3.user_id  
        WHERE  
            t3.age BETWEEN 18 AND 25  
            AND t2.singer_name = '周杰伦'  
            AND YEAR(fdate) = 2022  
    ),  
    b AS (  
        SELECT  
            month,  
            song_id,
            song_name,  
            COUNT(*) AS play_pv  
        FROM  
            a  
        GROUP BY  
            month, song_id,song_name  
    ),  
    c AS (  
        SELECT  
            month,  
            song_name,  
            play_pv,  
            ROW_NUMBER() OVER (  
                PARTITION BY month  
                ORDER BY play_pv DESC, song_id   
            ) AS ranking  
        FROM  
            b  
    )  
SELECT  
    month,  
    ranking,  
    song_name,  
    play_pv  
FROM  
    c  
WHERE  
    ranking <= 3  
ORDER BY  
    month,  
    ranking;