Spotify: Featured-Artist Credits in Track Titles per Label

Medium
940.

Spotify: Featured-Artist Credits in Track Titles per Label

Labels deliver track metadata to Spotify with featured artists written into the title, like Golden Hour (feat. Dusk Riders). The content-operations team audits each label's deliveries. A featured credit is one occurrence of the exact text (feat. (open bracket, feat., space) in title — a word like Defeat. is not a credit. The artists column lists every credited artist, separated by , (comma and space).

A track has a credit mismatch when its title names more featured credits than there are credited artists besides the first one (that is, more than the number of artists in artists minus 1).

For every label with at least one track, return label_name, tracks, feat_credits (featured credits across all its titles) and credit_mismatches (tracks with a mismatch). Order by feat_credits from most to fewest, then by label_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: RecordLabel

ColumnType
label_idint
label_namevarchar

Primary key: label_id.

One row per label or distributor delivering music; names are unique.

Table: TrackDelivery

ColumnType
track_idint
label_idint
titlevarchar
artistsvarchar

Primary key: track_id.

One row per delivered track. artists is never empty, never has a trailing separator, and no artist name contains a comma; all text is plain ASCII.

Example

The rows must come back in the order shown.

RecordLabel

label_idlabel_name
1Saffron Records
2Neon Coast Music
3Ghats Independent
4Monsoon Sound

TrackDelivery

track_idlabel_idtitleartists
11Monsoon Letters (feat. Kavya Rao)Aurora Lanes, Kavya Rao
21Golden Hour (feat. Dusk Riders) (feat. Echo Harbour)Aurora Lanes, Dusk Riders, Echo Harbour
31Paper PlanesAurora Lanes
42Chasing Neon (feat. Coral Static)Bombay Bassline
52Static Hearts (feat. Kavya Rao & Indigo Tides)Bombay Bassline, Kavya Rao, Indigo Tides
62Sweet Defeat. AgainBombay Bassline
73Quiet StormHampi Nights, Lunar Rickshaw
83Rooftop Echo (feat. Juhu Satellites) (feat. Firefly Theory)Hampi Nights, Juhu Satellites

Output

label_nametracksfeat_creditscredit_mismatches
Saffron Records330
Ghats Independent221
Neon Coast Music321
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: Featured-Artist Credits in Track Titles per Label

Labels deliver track metadata to Spotify with featured artists written into the title, like Golden Hour (feat. Dusk Riders).

  • Difficulty: Medium
  • Topics: Strings, Aggregation, Joins
  • Dialect: MySQL
  • Problem: #940

Problem statement

Labels deliver track metadata to Spotify with featured artists written into the title, like Golden Hour (feat. Dusk Riders). The content-operations team audits each label's deliveries. A featured credit is one occurrence of the exact text (feat. (open bracket, feat., space) in title — a word like Defeat. is not a credit. The artists column lists every credited artist, separated by , (comma and space).

A track has a credit mismatch when its title names more featured credits than there are credited artists besides the first one (that is, more than the number of artists in artists minus 1).

For every label with at least one track, return label_name, tracks, feat_credits (featured credits across all its titles) and credit_mismatches (tracks with a mismatch). Order by feat_credits from most to fewest, then by label_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: RecordLabel

ColumnType
label_idint
label_namevarchar

Primary key: label_id.

One row per label or distributor delivering music; names are unique.

Table: TrackDelivery

ColumnType
track_idint
label_idint
titlevarchar
artistsvarchar

Primary key: track_id.

One row per delivered track. artists is never empty, never has a trailing separator, and no artist name contains a comma; all text is plain ASCII.

Examples

Example 1

RecordLabel

label_idlabel_name
1Saffron Records
2Neon Coast Music
3Ghats Independent
4Monsoon Sound

TrackDelivery

track_idlabel_idtitleartists
11Monsoon Letters (feat. Kavya Rao)Aurora Lanes, Kavya Rao
21Golden Hour (feat. Dusk Riders) (feat. Echo Harbour)Aurora Lanes, Dusk Riders, Echo Harbour
31Paper PlanesAurora Lanes
42Chasing Neon (feat. Coral Static)Bombay Bassline
52Static Hearts (feat. Kavya Rao & Indigo Tides)Bombay Bassline, Kavya Rao, Indigo Tides
62Sweet Defeat. AgainBombay Bassline
73Quiet StormHampi Nights, Lunar Rickshaw
83Rooftop Echo (feat. Juhu Satellites) (feat. Firefly Theory)Hampi Nights, Juhu Satellites

Output

label_nametracksfeat_creditscredit_mismatches
Saffron Records330
Ghats Independent221
Neon Coast Music321

How to solve Spotify: Featured-Artist Credits in Track Titles per Label

SQL has no COUNT_OCCURRENCES function, but REPLACE gives one. Deleting every copy of a pattern p from s shortens it by LENGTH(p) characters per copy, so (LENGTH(s) − LENGTH(REPLACE(s, p, ''))) / LENGTH(p) is the number of copies. Here p is (feat. , seven characters: Golden Hour's title loses fourteen, so it has two credits.

Choosing the pattern is the real decision. Searching for plain feat. would count Sweet Defeat. Again as a featured credit; including the opening bracket and the trailing space matches only the credit format. For the credited artists, count the , separators the same way: with no trailing separator, the number of artists besides the first one is exactly the number of separators.

A mismatch is a title naming more guests than are credited: Chasing Neon names one guest with none credited, and Rooftop Echo names two with one credited. Static Hearts names two artists inside one (feat. …), which is one credit by this rule, and credits both — not a mismatch. Per label, COUNT(*), SUM(feats) and a conditional SUM give the three columns; labels without deliveries never join.

The alternative uses a neat variant: replace (feat. with (feat. — one character shorter — so the length difference is the count with no division, and counts single commas in artists (names contain no commas). Both are one scan of the deliveries.

Reference solution (MySQL)

WITH counted AS (
  SELECT label_id,
         (LENGTH(title) - LENGTH(REPLACE(title, '(feat. ', ''))) / LENGTH('(feat. ') AS feats,
         (LENGTH(artists) - LENGTH(REPLACE(artists, ', ', ''))) / LENGTH(', ') AS guests_credited
  FROM TrackDelivery
)
SELECT l.label_name, COUNT(*) AS tracks, SUM(c.feats) AS feat_credits,
       SUM(CASE WHEN c.feats > c.guests_credited THEN 1 ELSE 0 END) AS credit_mismatches
FROM counted c
JOIN RecordLabel l ON l.label_id = c.label_id
GROUP BY l.label_id, l.label_name
ORDER BY feat_credits DESC, l.label_name

Another way

SELECT l.label_name, COUNT(*) AS tracks,
       SUM(CHAR_LENGTH(t.title) - CHAR_LENGTH(REPLACE(t.title, '(feat. ', '(feat.'))) AS feat_credits,
       SUM(IF(CHAR_LENGTH(t.title) - CHAR_LENGTH(REPLACE(t.title, '(feat. ', '(feat.'))
              > CHAR_LENGTH(t.artists) - CHAR_LENGTH(REPLACE(t.artists, ',', '')), 1, 0)) AS credit_mismatches
FROM TrackDelivery t
JOIN RecordLabel l ON l.label_id = t.label_id
GROUP BY l.label_id, l.label_name
ORDER BY feat_credits DESC, l.label_name

← Spotify: Listeners Who Played an Album Front to Back · Spotify: Artists Growing Year Over Year in Every Market →