Spotify: Artists Who Released Nothing in 2024 — SQL Problem
Artist relations at Spotify reaches out to artists who went a whole year without putting out music, to pitch the tools for planning a release.
- Difficulty: Easy
- Topics: Joins, Subqueries, Dates
- Dialect: MySQL
- Problem: #927
Problem statement
Artist relations at Spotify reaches out to artists who went a whole year without putting out music, to pitch the tools for planning a release.
Return every artist with no release dated in 2024 — artists whose releases are all in other years, and artists with no releases at all — with the columns artist_id and artist_name. Return the rows in any order.
A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.
Tables
Table: Artist
| Column | Type |
|---|---|
| artist_id | int |
| artist_name | varchar |
| home_country | char |
Primary key: artist_id.
One row per artist profile.
Table: ArtistRelease
| Column | Type |
|---|---|
| release_id | int |
| artist_id | int |
| release_type | enum(single, ep, album) |
| released_on | date |
Primary key: release_id.
One row per single, EP or album; artist_id always names an artist.
Examples
Example 1
Artist
| artist_id | artist_name | home_country |
|---|---|---|
| 1 | Aurora Lanes | IN |
| 2 | Bombay Bassline | IN |
| 3 | Coral Static | GB |
| 4 | Dusk Riders | US |
| 5 | Echo Harbour | IN |
ArtistRelease
| release_id | artist_id | release_type | released_on |
|---|---|---|---|
| 10 | 1 | single | 2024-02-14 |
| 11 | 1 | album | 2023-11-03 |
| 12 | 2 | ep | 2023-12-31 |
| 13 | 2 | single | 2025-01-01 |
| 14 | 3 | album | 2024-12-31 |
| 15 | 5 | single | 2022-06-10 |
Output
| artist_id | artist_name |
|---|---|
| 2 | Bombay Bassline |
| 4 | Dusk Riders |
| 5 | Echo Harbour |
How to solve Spotify: Artists Who Released Nothing in 2024
The question is about a row that is missing — a 2024 release — so it is an anti join. NOT EXISTS states it directly: keep an artist when no release of theirs falls between 1 January and 31 December 2024. An artist with only 2023 and 2025 releases passes, and so does an artist with no releases at all, because the subquery finds nothing either way.
Where the date condition sits matters. In the LEFT JOIN alternative the year test is in ON, so the join only matches 2024 releases and r.release_id IS NULL keeps the artists that matched none. Moving YEAR(...) = 2024 into WHERE would throw away exactly the unmatched rows we want. The NOT IN version is safe here because ArtistRelease.artist_id is never NULL — a single NULL in the list would make NOT IN return nothing.
Both boundary days count as 2024; the hidden datasets test 31 December 2023 and 1 January 2025 too. With an index on (artist_id, released_on) each artist costs one index probe.
Reference solution (MySQL)
SELECT a.artist_id, a.artist_name
FROM Artist a
WHERE NOT EXISTS (
SELECT 1 FROM ArtistRelease r
WHERE r.artist_id = a.artist_id
AND r.released_on BETWEEN '2024-01-01' AND '2024-12-31'
)Another way
SELECT a.artist_id, a.artist_name FROM Artist a LEFT JOIN ArtistRelease r ON r.artist_id = a.artist_id AND YEAR(r.released_on) = 2024 WHERE r.release_id IS NULLAnother way
SELECT artist_id, artist_name FROM Artist WHERE artist_id NOT IN (SELECT artist_id FROM ArtistRelease WHERE released_on >= '2024-01-01' AND released_on < '2025-01-01')← Spotify: Family and Duo Plans With Open Seats · Spotify: Student Plans Due for Re-Verification →