Spotify: Songwriter Splits That Don't Add Up to 100%
Before publishing royalties can be paid on a track, its songwriter split sheet must share out exactly 100%.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #938
Problem statement
Before publishing royalties can be paid on a track, its songwriter split sheet must share out exactly 100%. The publishing operations team wants every track that is not ready: splits that total more or less than 100, and tracks with no split rows at all.
Return those tracks with the columns track_id, title, writers (number of split rows) and total_share (the sum of share_pct, 0 for a track with no splits). Order by track_id.
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: Track
| Column | Type |
|---|---|
| track_id | int |
| title | varchar |
| release_date | date |
Primary key: track_id.
One row per recording.
Table: WriterSplit
| Column | Type |
|---|---|
| track_id | int |
| writer_name | varchar |
| share_pct | int |
Primary key: track_id, writer_name.
One row per songwriter credited on a track, with their share in whole percent.
Examples
Example 1
Track
| track_id | title | release_date |
|---|---|---|
| 1 | Monsoon Letters | 2024-01-12 |
| 2 | Chasing Neon | 2024-02-02 |
| 3 | Paper Planes | 2024-03-15 |
| 4 | Golden Hour | 2024-04-19 |
| 5 | Static Hearts | 2024-05-24 |
WriterSplit
| track_id | writer_name | share_pct |
|---|---|---|
| 1 | Kavya Rao | 50 |
| 1 | Vikram Iyer | 50 |
| 2 | Neha Das | 40 |
| 2 | Arjun Nair | 40 |
| 3 | Ishaan Bose | 60 |
| 3 | Priya Menon | 30 |
| 3 | Dev Rao | 20 |
| 5 | Simran Joshi | 100 |
Output
| track_id | title | writers | total_share |
|---|---|---|---|
| 2 | Chasing Neon | 2 | 80 |
| 3 | Paper Planes | 3 | 110 |
| 4 | Golden Hour | 0 | 0 |
How to solve Spotify: Songwriter Splits That Don't Add Up to 100%
The check is a sum per track compared with 100, so the core is GROUP BY track_id with SUM(share_pct). The interesting part is the track with no split rows: it is the least ready of all, but an inner join would never see it, and even with a LEFT JOIN its SUM is NULL. NULL <> 100 evaluates to NULL, which HAVING treats as false — the track would silently vanish. COALESCE(SUM(...), 0) turns it into 0, which is not 100, so it is reported with total_share 0.
COUNT(w.writer_name) counts only matched split rows, giving 0 writers for such a track, whereas COUNT(*) would count the one NULL-padded row. Shares are whole percents, so the comparison with 100 is exact; with fractional shares like 33.33 you would compare against a tolerance or store basis points.
The alternatives aggregate the splits in a derived table first and test IS NULL OR <> 100 after the LEFT JOIN, or compute both figures with correlated subqueries. All read the split table once per track or once in total; the grouped LEFT JOIN is the simplest single pass.
Reference solution (MySQL)
SELECT t.track_id, t.title, COUNT(w.writer_name) AS writers, COALESCE(SUM(w.share_pct), 0) AS total_share
FROM Track t
LEFT JOIN WriterSplit w ON w.track_id = t.track_id
GROUP BY t.track_id, t.title
HAVING COALESCE(SUM(w.share_pct), 0) <> 100
ORDER BY t.track_idAnother way
SELECT t.track_id, t.title, COALESCE(s.writers, 0) AS writers, COALESCE(s.total_share, 0) AS total_share
FROM Track t
LEFT JOIN (SELECT track_id, COUNT(*) AS writers, SUM(share_pct) AS total_share FROM WriterSplit GROUP BY track_id) s ON s.track_id = t.track_id
WHERE s.total_share IS NULL OR s.total_share <> 100
ORDER BY t.track_idAnother way
SELECT track_id, title, (SELECT COUNT(*) FROM WriterSplit w WHERE w.track_id = t.track_id) AS writers, IFNULL((SELECT SUM(share_pct) FROM WriterSplit w WHERE w.track_id = t.track_id), 0) AS total_share FROM Track t WHERE IFNULL((SELECT SUM(share_pct) FROM WriterSplit w WHERE w.track_id = t.track_id), 0) <> 100 ORDER BY track_id← Spotify: Shared Plans With Members at Different Addresses · Spotify: Listeners Who Played an Album Front to Back →