Spotify: Artists Growing Year Over Year in Every Market
The artist-growth team wants a list of artists whose growth is broad, not carried by one country.
- Difficulty: Hard
- Topics: Aggregation, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #941
Problem statement
The artist-growth team wants a list of artists whose growth is broad, not carried by one country. It compares the first half of each year: H1 2023 is 2023-01-01 to 2023-06-30 and H1 2024 is 2024-01-01 to 2024-06-30, both inclusive. Rows outside those two windows are ignored.
Per artist and market, add up the streams in each window. A market where the artist had streams in H1 2023 is a 2023 market; one with streams in H1 2024 but none in H1 2023 is a new market. An artist qualifies when they have at least two 2023 markets and every 2023 market grew — H1 2024 streams strictly greater than H1 2023 (a market with no H1 2024 streams has 0, so it shrank).
Return each qualifying artist with the columns artist_name, markets_2023, new_markets, streams_2023 and streams_2024 (the artist's H1 totals across all markets). Order by artist_name.
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 |
Primary key: artist_id.
One row per artist; names are unique.
Table: DailyArtistStreams
| Column | Type |
|---|---|
| artist_id | int |
| market | char |
| stream_date | date |
| streams | int |
Primary key: artist_id, market, stream_date.
An artist's streams in one market on one day. Days with no streams have no row, so streams is always at least 1.
Examples
Example 1
Artist
| artist_id | artist_name |
|---|---|
| 1 | Aurora Lanes |
| 2 | Bombay Bassline |
| 3 | Coral Static |
| 4 | Dusk Riders |
| 5 | Echo Harbour |
DailyArtistStreams
| artist_id | market | stream_date | streams |
|---|---|---|---|
| 1 | IN | 2023-02-10 | 600 |
| 1 | IN | 2023-05-01 | 400 |
| 1 | IN | 2024-03-03 | 1500 |
| 1 | US | 2023-06-30 | 400 |
| 1 | US | 2024-01-01 | 900 |
| 1 | BR | 2024-04-12 | 300 |
| 2 | IN | 2023-03-15 | 800 |
| 2 | IN | 2024-03-15 | 1200 |
| 2 | GB | 2023-04-04 | 500 |
| 2 | GB | 2024-04-04 | 500 |
| 3 | IN | 2023-01-20 | 300 |
| 3 | IN | 2024-01-20 | 600 |
| 3 | US | 2024-02-02 | 250 |
| 4 | IN | 2023-02-01 | 700 |
| 4 | IN | 2024-02-01 | 900 |
| 4 | US | 2023-03-09 | 200 |
| 4 | US | 2024-07-02 | 5000 |
| 5 | DE | 2023-06-01 | 100 |
| 5 | DE | 2024-02-29 | 150 |
| 5 | ID | 2023-01-01 | 120 |
| 5 | ID | 2024-06-30 | 130 |
| 5 | GB | 2023-07-15 | 90 |
Output
| artist_name | markets_2023 | new_markets | streams_2023 | streams_2024 |
|---|---|---|---|---|
| Aurora Lanes | 2 | 1 | 1400 | 2700 |
| Echo Harbour | 2 | 0 | 220 | 280 |
How to solve Spotify: Artists Growing Year Over Year in Every Market
A year-over-year comparison needs the two years side by side in one row, and conditional aggregation is the pivot that puts them there. Grouping the daily rows by artist and market, SUM(CASE WHEN stream_date BETWEEN '2023-01-01' AND '2023-06-30' THEN streams ELSE 0 END) is the H1 2023 total and the same with the 2024 window the H1 2024 total. Rows outside both windows contribute 0 to each, so Echo Harbour's July 2023 row in GB makes GB neither a 2023 market nor a new one. The windows are inclusive: 30 June and 1 January count, and 29 February 2024 is just another H1 day.
The second level groups those pairs per artist. A 2023 market is one with s23 > 0; a new market has s23 = 0 and s24 > 0. The universal condition "every 2023 market grew" becomes a count of counterexamples that must be zero: SUM(CASE WHEN s23 > 0 AND s24 <= s23 THEN 1 ELSE 0 END) = 0. Equal streams are not growth (Bombay Bassline's GB), and a market that went silent in H1 2024 has s24 = 0 and fails — Dusk Riders' only 2024 US streams came in July. Coral Static grew, but in a single 2023 market, short of the two required. Aurora Lanes (two growing markets and a new one in BR) and Echo Harbour qualify.
The alternative aggregates by YEAR() first and rebuilds the side-by-side shape with two LEFT JOINs from the distinct artist–market pairs, treating a missing year as NULL. It must remember that a missing 2024 is a fall (COALESCE(n.s, 0)). The pivot reads the table once and needs no join, which is why it is the usual way to write period-over-period reports.
Reference solution (MySQL)
WITH per_market AS (
SELECT artist_id, market,
SUM(CASE WHEN stream_date BETWEEN '2023-01-01' AND '2023-06-30' THEN streams ELSE 0 END) AS s23,
SUM(CASE WHEN stream_date BETWEEN '2024-01-01' AND '2024-06-30' THEN streams ELSE 0 END) AS s24
FROM DailyArtistStreams
GROUP BY artist_id, market
)
SELECT a.artist_name,
SUM(CASE WHEN pm.s23 > 0 THEN 1 ELSE 0 END) AS markets_2023,
SUM(CASE WHEN pm.s23 = 0 AND pm.s24 > 0 THEN 1 ELSE 0 END) AS new_markets,
SUM(pm.s23) AS streams_2023,
SUM(pm.s24) AS streams_2024
FROM per_market pm
JOIN Artist a ON a.artist_id = pm.artist_id
GROUP BY a.artist_id, a.artist_name
HAVING SUM(CASE WHEN pm.s23 > 0 THEN 1 ELSE 0 END) >= 2
AND SUM(CASE WHEN pm.s23 > 0 AND pm.s24 <= pm.s23 THEN 1 ELSE 0 END) = 0
ORDER BY a.artist_nameAnother way
WITH y AS (SELECT artist_id, market, YEAR(stream_date) AS yr, SUM(streams) AS s
FROM DailyArtistStreams
WHERE YEAR(stream_date) IN (2023, 2024) AND MONTH(stream_date) <= 6
GROUP BY artist_id, market, YEAR(stream_date)),
m AS (SELECT DISTINCT artist_id, market FROM y)
SELECT a.artist_name,
COUNT(o.s) AS markets_2023,
SUM(CASE WHEN o.s IS NULL THEN 1 ELSE 0 END) AS new_markets,
COALESCE(SUM(o.s), 0) AS streams_2023,
COALESCE(SUM(n.s), 0) AS streams_2024
FROM m
JOIN Artist a ON a.artist_id = m.artist_id
LEFT JOIN y o ON o.artist_id = m.artist_id AND o.market = m.market AND o.yr = 2023
LEFT JOIN y n ON n.artist_id = m.artist_id AND n.market = m.market AND n.yr = 2024
GROUP BY a.artist_id, a.artist_name
HAVING COUNT(o.s) >= 2
AND SUM(CASE WHEN o.s IS NOT NULL AND COALESCE(n.s, 0) <= o.s THEN 1 ELSE 0 END) = 0
ORDER BY a.artist_name← Spotify: Featured-Artist Credits in Track Titles per Label · Spotify: Podcast Episode-to-Episode Listener Retention →