WITH genre_year AS (
    SELECT 
        playlist_genre AS Genre,
        EXTRACT(YEAR FROM track_album_release_date) AS Year,
        COUNT(track_id) AS Song_Count
    FROM @kaggle.joebeachcapital_30000_spotify_songs.spotify_songs
    GROUP BY 
        playlist_genre, 
        EXTRACT(YEAR FROM track_album_release_date)
)
SELECT 
    Year,
    SUM(CASE WHEN Genre = 'pop' THEN Song_Count ELSE 0 END) AS Pop,
    SUM(CASE WHEN Genre = 'rock' THEN Song_Count ELSE 0 END) AS Rock,
    SUM(CASE WHEN Genre = 'rap' THEN Song_Count ELSE 0 END) AS Rap,
    SUM(CASE WHEN Genre = 'latin' THEN Song_Count ELSE 0 END) AS Latin,
    SUM(CASE WHEN Genre = 'edm' THEN Song_Count ELSE 0 END) AS Electronic,
FROM 
    genre_year
GROUP BY 
    Year
ORDER BY 
    Year;