Spotify: Artists Growing Year Over Year in Every Market

Hard
941.

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. 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

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist; names are unique.

Table: DailyArtistStreams

ColumnType
artist_idint
marketchar
stream_datedate
streamsint

Primary key (together): 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.

Example

The rows must come back in the order shown.

Artist

artist_idartist_name
1Aurora Lanes
2Bombay Bassline
3Coral Static
4Dusk Riders
5Echo Harbour

DailyArtistStreams

artist_idmarketstream_datestreams
1IN2023-02-10600
1IN2023-05-01400
1IN2024-03-031500
1US2023-06-30400
1US2024-01-01900
1BR2024-04-12300
2IN2023-03-15800
2IN2024-03-151200
2GB2023-04-04500
2GB2024-04-04500
12 more rows

Output

artist_namemarkets_2023new_marketsstreams_2023streams_2024
Aurora Lanes2114002700
Echo Harbour20220280
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 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

ColumnType
artist_idint
artist_namevarchar

Primary key: artist_id.

One row per artist; names are unique.

Table: DailyArtistStreams

ColumnType
artist_idint
marketchar
stream_datedate
streamsint

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_idartist_name
1Aurora Lanes
2Bombay Bassline
3Coral Static
4Dusk Riders
5Echo Harbour

DailyArtistStreams

artist_idmarketstream_datestreams
1IN2023-02-10600
1IN2023-05-01400
1IN2024-03-031500
1US2023-06-30400
1US2024-01-01900
1BR2024-04-12300
2IN2023-03-15800
2IN2024-03-151200
2GB2023-04-04500
2GB2024-04-04500
3IN2023-01-20300
3IN2024-01-20600
3US2024-02-02250
4IN2023-02-01700
4IN2024-02-01900
4US2023-03-09200
4US2024-07-025000
5DE2023-06-01100
5DE2024-02-29150
5ID2023-01-01120
5ID2024-06-30130
5GB2023-07-1590

Output

artist_namemarkets_2023new_marketsstreams_2023streams_2024
Aurora Lanes2114002700
Echo Harbour20220280

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_name

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