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
| Column | Type |
|---|---|
| label_id | int |
| label_name | varchar |
Primary key: label_id.
One row per label or distributor delivering music; names are unique.
Table: TrackDelivery
| Column | Type |
|---|---|
| track_id | int |
| label_id | int |
| title | varchar |
| artists | varchar |
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_id | label_name |
|---|---|
| 1 | Saffron Records |
| 2 | Neon Coast Music |
| 3 | Ghats Independent |
| 4 | Monsoon Sound |
TrackDelivery
| track_id | label_id | title | artists |
|---|---|---|---|
| 1 | 1 | Monsoon Letters (feat. Kavya Rao) | Aurora Lanes, Kavya Rao |
| 2 | 1 | Golden Hour (feat. Dusk Riders) (feat. Echo Harbour) | Aurora Lanes, Dusk Riders, Echo Harbour |
| 3 | 1 | Paper Planes | Aurora Lanes |
| 4 | 2 | Chasing Neon (feat. Coral Static) | Bombay Bassline |
| 5 | 2 | Static Hearts (feat. Kavya Rao & Indigo Tides) | Bombay Bassline, Kavya Rao, Indigo Tides |
| 6 | 2 | Sweet Defeat. Again | Bombay Bassline |
| 7 | 3 | Quiet Storm | Hampi Nights, Lunar Rickshaw |
| 8 | 3 | Rooftop Echo (feat. Juhu Satellites) (feat. Firefly Theory) | Hampi Nights, Juhu Satellites |
Output
| label_name | tracks | feat_credits | credit_mismatches |
|---|---|---|---|
| Saffron Records | 3 | 3 | 0 |
| Ghats Independent | 2 | 2 | 1 |
| Neon Coast Music | 3 | 2 | 1 |
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_nameAnother 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 →