Spotify: Artists Who Released Nothing in 2024

Easy
927.

Spotify: Artists Who Released Nothing in 2024

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

ColumnType
artist_idint
artist_namevarchar
home_countrychar

Primary key: artist_id.

One row per artist profile.

Table: ArtistRelease

ColumnTypeValues
release_idint
artist_idint
release_typeenumsingle, ep, album
released_ondate

Primary key: release_id.

One row per single, EP or album; artist_id always names an artist.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Artist

artist_idartist_namehome_country
1Aurora LanesIN
2Bombay BasslineIN
3Coral StaticGB
4Dusk RidersUS
5Echo HarbourIN

ArtistRelease

release_idartist_idrelease_typereleased_on
101single2024-02-14
111album2023-11-03
122ep2023-12-31
132single2025-01-01
143album2024-12-31
155single2022-06-10

Output

artist_idartist_name
2Bombay Bassline
4Dusk Riders
5Echo Harbour
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
artist_idint
artist_namevarchar
home_countrychar

Primary key: artist_id.

One row per artist profile.

Table: ArtistRelease

ColumnType
release_idint
artist_idint
release_typeenum(single, ep, album)
released_ondate

Primary key: release_id.

One row per single, EP or album; artist_id always names an artist.

Examples

Example 1

Artist

artist_idartist_namehome_country
1Aurora LanesIN
2Bombay BasslineIN
3Coral StaticGB
4Dusk RidersUS
5Echo HarbourIN

ArtistRelease

release_idartist_idrelease_typereleased_on
101single2024-02-14
111album2023-11-03
122ep2023-12-31
132single2025-01-01
143album2024-12-31
155single2022-06-10

Output

artist_idartist_name
2Bombay Bassline
4Dusk Riders
5Echo 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 NULL

Another 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 →