Spotify: Artists Not Touring Their Top Listening City

Medium
936.

Spotify: Artists Not Touring Their Top Listening City

Spotify shows artists where their listeners are, so that tour routing follows demand. An artist's top city is the city with the most distinct listeners who played the artist (a listener's city is the one on their profile); when two cities tie, the one that comes first alphabetically is the top city.

Return every artist whose top city has no concert of theirs in the Concert table, with the columns artist_name, top_city and listeners (distinct listeners in that city). Artists nobody played are not in the answer. 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

Primary key: artist_id.

One row per artist; names are unique.

Table: Listener

ColumnType
listener_idint
cityvarchar

Primary key: listener_id.

One row per listener and the city on their profile.

Table: Play

ColumnType
play_idint
listener_idint
artist_idint
played_ondate

Primary key: play_id.

One row per play; one listener may play an artist many times.

Table: Concert

ColumnType
concert_idint
artist_idint
cityvarchar
show_datedate

Primary key: concert_id.

One row per listed concert, past or upcoming.

Example

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

Artist

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

Listener

listener_idcity
1Mumbai
2Mumbai
3Pune
4Delhi
5Delhi
6Bengaluru

Play

play_idlistener_idartist_idplayed_on
1112024-08-01
2112024-08-02
3112024-08-03
4312024-08-04
5412024-08-04
6512024-08-05
7122024-08-01
8222024-08-02
9622024-08-03
10432024-08-03
1 more row

Concert

concert_idartist_idcityshow_date
12Mumbai2024-12-14
21Pune2025-01-18
33Delhi2024-11-02

Output

artist_nametop_citylisteners
Aurora LanesDelhi2
Coral StaticBengaluru1
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 Not Touring Their Top Listening City

Spotify shows artists where their listeners are, so that tour routing follows demand.

  • Difficulty: Medium
  • Topics: Window Functions, Subqueries, Joins
  • Dialect: MySQL
  • Problem: #936

Problem statement

Spotify shows artists where their listeners are, so that tour routing follows demand. An artist's top city is the city with the most distinct listeners who played the artist (a listener's city is the one on their profile); when two cities tie, the one that comes first alphabetically is the top city.

Return every artist whose top city has no concert of theirs in the Concert table, with the columns artist_name, top_city and listeners (distinct listeners in that city). Artists nobody played are not in the answer. 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

Primary key: artist_id.

One row per artist; names are unique.

Table: Listener

ColumnType
listener_idint
cityvarchar

Primary key: listener_id.

One row per listener and the city on their profile.

Table: Play

ColumnType
play_idint
listener_idint
artist_idint
played_ondate

Primary key: play_id.

One row per play; one listener may play an artist many times.

Table: Concert

ColumnType
concert_idint
artist_idint
cityvarchar
show_datedate

Primary key: concert_id.

One row per listed concert, past or upcoming.

Examples

Example 1

Artist

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

Listener

listener_idcity
1Mumbai
2Mumbai
3Pune
4Delhi
5Delhi
6Bengaluru

Play

play_idlistener_idartist_idplayed_on
1112024-08-01
2112024-08-02
3112024-08-03
4312024-08-04
5412024-08-04
6512024-08-05
7122024-08-01
8222024-08-02
9622024-08-03
10432024-08-03
11632024-08-03

Concert

concert_idartist_idcityshow_date
12Mumbai2024-12-14
21Pune2025-01-18
33Delhi2024-11-02

Output

artist_nametop_citylisteners
Aurora LanesDelhi2
Coral StaticBengaluru1

How to solve Spotify: Artists Not Touring Their Top Listening City

Three steps. First, reach per city: join plays to listeners to get each play's city, then COUNT(DISTINCT listener_id) per artist and city. Counting plays would let one superfan outweigh a city full of casual listeners — in the example listener 1 plays Aurora Lanes three times, but Mumbai still has only one Aurora listener, as does Pune, while Delhi has two.

Second, the top city: ROW_NUMBER() per artist ordered by listeners descending, then city ascending, so a tie resolves to the alphabetically first city and each artist gets exactly one. Bombay Bassline has two Mumbai listeners and one in Bengaluru, so Mumbai is top; Coral Static ties one–one between Bengaluru and Delhi, and Bengaluru wins the tie.

Third, an anti join against Concert on both artist and city: Bombay Bassline already plays Mumbai, so it is fine; Aurora Lanes has a Pune date but its top city is Delhi, so it is reported. The alternative finds the top city with NOT EXISTS a better city and the missing concert with a LEFT JOIN … IS NULL. The cost is one aggregation over plays plus small sorts.

Reference solution (MySQL)

WITH reach AS (
  SELECT p.artist_id, l.city, COUNT(DISTINCT p.listener_id) AS listeners
  FROM Play p
  JOIN Listener l ON l.listener_id = p.listener_id
  GROUP BY p.artist_id, l.city
), best AS (
  SELECT artist_id, city, listeners,
         ROW_NUMBER() OVER (PARTITION BY artist_id ORDER BY listeners DESC, city) AS rn
  FROM reach
)
SELECT a.artist_name, b.city AS top_city, b.listeners
FROM best b
JOIN Artist a ON a.artist_id = b.artist_id
WHERE b.rn = 1
  AND NOT EXISTS (SELECT 1 FROM Concert c WHERE c.artist_id = b.artist_id AND c.city = b.city)

Another way

WITH reach AS (SELECT p.artist_id, l.city, COUNT(DISTINCT p.listener_id) AS listeners FROM Play p JOIN Listener l ON l.listener_id = p.listener_id GROUP BY p.artist_id, l.city)
SELECT DISTINCT a.artist_name, r.city AS top_city, r.listeners
FROM reach r
JOIN Artist a ON a.artist_id = r.artist_id
LEFT JOIN Concert c ON c.artist_id = r.artist_id AND c.city = r.city
WHERE c.concert_id IS NULL
  AND NOT EXISTS (SELECT 1 FROM reach o WHERE o.artist_id = r.artist_id
                  AND (o.listeners > r.listeners OR (o.listeners = r.listeners AND o.city < r.city)))

← Spotify: Ad Load by Market Against the Four-an-Hour Cap · Spotify: Shared Plans With Members at Different Addresses →