Skip to content

Instantly share code, notes, and snippets.

@devheedoo

devheedoo/spotify_queries.sql

Created November 23, 2025 16:56
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save devheedoo/f8f73529f34a8f906f406669d7d316a2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save devheedoo/f8f73529f34a8f906f406669d7d316a2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
spotify_queries.sql
-- Challenge 1: List all album names with their artist names (use appropriate through table)
SELECT a.name
FROM albums a
JOIN r_albums_artists raa
ON a.id = raa.album_id
JOIN artists a2
ON a2.id = raa.artist_id;
-- Challenge 2: Show all tracks with their album names
SELECT t.name
FROM tracks t
LEFT OUTER JOIN r_albums_tracks rat
ON t.id = rat.track_id
LEFT OUTER JOIN albums a
ON a.id = rat.album_id;
-- Challenge 3: Display artists with their genres
SELECT a.name
FROM artists a
LEFT OUTER JOIN r_artist_genre rag
ON a.id = rag.artist_id
LEFT OUTER JOIN genres g
ON g.id = rag.genre_id;
-- Challenge 4: Find all tracks with their artist names and album names in one query
SELECT
t.name,
a.name,
a2.name
FROM tracks t
LEFT OUTER JOIN r_track_artist rta
ON t.id = rta.track_id
LEFT OUTER JOIN artists a
ON a.id = rta.artist_id
LEFT OUTER JOIN r_albums_tracks rat
ON t.id = rat.track_id
LEFT OUTER JOIN albums a2
ON a2.id = rat.album_id;
-- Challenge 5: List albums with all their artiests (some albums have multiple artists)
SELECT
a.name AS album_name,
GROUP_CONCAT(a2.name, ', ') AS artist_names
FROM albums a
LEFT OUTER JOIN r_albums_artists raa
ON a.id = raa.album_id
LEFT OUTER JOIN artists a2
ON a2.id = raa.artist_id
GROUP BY a.id;
-- Challenge 6: Show tracks with their audio features (danceability, energy, etc.)
SELECT
t.name,
af.*
FROM tracks t
LEFT OUTER JOIN audio_features af
ON t.audio_feature_id = af.id;
-- Challenge 7: Find the average popularity of tracks for each artist (minimum 5 tracks)
SELECT
a.name,
AVG(t.popularity) AS average_popularity
FROM artists a
JOIN r_track_artist rta
ON a.id = rta.artist_id
JOIN tracks t
ON t.id = rta.track_id
GROUP BY a.id
HAVING COUNT(*) >= 5;
-- Challenege 8: Count how many tracks each album contains
SELECT
a.name,
COUNT(*) AS tracks_count
FROM albums a
JOIN r_albums_tracks rat
ON a.id = rat.album_id
JOIN tracks t
ON t.id = rat.track_id
GROUP BY a.id;
-- Challenge 9: Find artists with the most albums
WITH counts AS (
SELECT
a.name,
COUNT(*) AS albums_count
FROM artists a
JOIN r_albums_artists raa
ON a.id = raa.artist_id
GROUP BY a.id
)
SELECT *
FROM counts
WHERE albums_count = (SELECT MAX(albums_count) FROM counts);
-- Challenge 10: Find the most "danceable" track (highest danceability score) for each artist
SELECT
artist_name,
track_name,
danceability
FROM (
SELECT
a.id AS artist_id,
a.name AS artist_name,
t.id AS track_id,
t.name AS track_name,
af.danceability,
ROW_NUMBER() OVER (
PARTITION BY a.id
ORDER BY af.danceability DESC
) AS rn
FROM artists a
JOIN r_track_artist rta ON a.id = rta.artist_id
JOIN tracks t ON t.id = rta.track_id
JOIN audio_features af ON af.id = t.audio_feature_id
)
WHERE rn = 1;
-- Challenge 11: List albums released in 2020 (hint: convert milliseconds to year) with their artists
SELECT
al.id AS album_id,
al.name AS album_name,
GROUP_CONCAT(ar.name, ', ') AS artists
FROM albums al
JOIN r_albums_artists raa ON al.id = raa.album_id
JOIN artists ar ON ar.id = raa.artist_id
WHERE STRFTIME('%Y', al.release_date / 1000, 'unixepoch') = '2020'
GROUP BY al.id, al.name;
-- Challenge 12: Find artists who appear on albums as collaborators but don't have their own albums
SELECT DISTINCT ar.name
FROM artists ar
JOIN r_track_artist rta
ON ar.id = rta.artist_id -- appears on tracks (collaborator)
WHERE ar.id NOT IN (
SELECT artist_id
FROM r_albums_artists -- but not associated with any album
);
-- Challenge 13: Using JOINs and subqueries, find the artist with the highest average track energy
WITH energy_stats AS (
SELECT
rta.artist_id,
AVG(af.energy) AS avg_energy
FROM r_track_artist rta
JOIN tracks t ON t.id = rta.track_id
JOIN audio_features af ON af.id = t.audio_feature_id
GROUP BY rta.artist_id
),
max_energy AS (
SELECT MAX(avg_energy) AS top_avg
FROM energy_stats
)
SELECT
a.id,
a.name,
es.avg_energy
FROM energy_stats es
JOIN max_energy me ON es.avg_energy = me.top_avg
JOIN artists a ON a.id = es.artist_id;
-- Challenge 14: Create a query that shows artists who have both high-energy tracks (energy > 0.8) and low-energy tracks (energy < 0.3)
SELECT
a.id AS artist_id,
a.name AS artist_name
FROM artists a
JOIN r_track_artist rta ON a.id = rta.artist_id
JOIN tracks t ON t.id = rta.track_id
JOIN audio_features af ON af.id = t.audio_feature_id
GROUP BY a.id, a.name
HAVING
SUM(CASE WHEN af.energy > 0.8 THEN 1 ELSE 0 END) > 0
AND
SUM(CASE WHEN af.energy < 0.3 THEN 1 ELSE 0 END) > 0;
-- Challenge 15: Find albums with more than 10 tracks
SELECT
al.id AS album_id,
al.name AS album_name,
COUNT(rat.track_id) AS track_count
FROM albums al
JOIN r_albums_tracks rat ON al.id = rat.album_id
GROUP BY al.id, al.name
HAVING COUNT(rat.track_id) > 10
ORDER BY track_count DESC;
-- Challenge 16: Find the most popular track for each genre.
WITH genre_tracks AS (
SELECT
g.genre_id,
t.id AS track_id,
t.name AS track_name,
t.popularity,
ROW_NUMBER() OVER (
PARTITION BY g.genre_id
ORDER BY t.popularity DESC
) AS rn
FROM r_artist_genre g
JOIN r_track_artist rta ON g.artist_id = rta.artist_id
JOIN tracks t ON t.id = rta.track_id
)
SELECT
genre_id,
track_name,
popularity
FROM genre_tracks
WHERE rn = 1
ORDER BY popularity DESC;
-- Challenge 17: Find the genre with the highest average track popularity
SELECT
g.genre_id,
AVG(t.popularity) AS avg_popularity
FROM r_artist_genre g
JOIN r_track_artist rta ON g.artist_id = rta.artist_id
JOIN tracks t ON t.id = rta.track_id
GROUP BY g.genre_id
ORDER BY avg_popularity DESC
LIMIT 1;
-- Challenge 18: Find tracks longer than 5 minutes (300000 ms) with their artists
SELECT
t.id AS track_id,
t.name AS track_name,
t.duration,
ar.name AS artist_name
FROM tracks t
JOIN r_track_artist rta ON t.id = rta.track_id
JOIN artists ar ON ar.id = rta.artist_id
WHERE t.duration > 300000 -- longer than 5 minutes
ORDER BY t.duration DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment