1WITH genre_year AS (
2    SELECT 
3        playlist_genre AS Genre,
4        EXTRACT(YEAR FROM track_album_release_date) AS Year,
5        COUNT(track_id) AS Song_Count
6    FROM @kaggle.joebeachcapital_30000_spotify_songs.spotify_songs
7    GROUP BY 
8        playlist_genre, 
9        EXTRACT(YEAR FROM track_album_release_date)
10)
11SELECT 
12    Year,
13    SUM(CASE WHEN Genre = 'pop' THEN Song_Count ELSE 0 END) AS Pop,
14    SUM(CASE WHEN Genre = 'rock' THEN Song_Count ELSE 0 END) AS Rock,
15    SUM(CASE WHEN Genre = 'rap' THEN Song_Count ELSE 0 END) AS Rap,
16    SUM(CASE WHEN Genre = 'latin' THEN Song_Count ELSE 0 END) AS Latin,
17    SUM(CASE WHEN Genre = 'edm' THEN Song_Count ELSE 0 END) AS Electronic,
18FROM 
19    genre_year
20GROUP BY 
21    Year
22ORDER BY 
23    Year;
24