Spotify: Songwriter Splits That Don't Add Up to 100%

Medium
938.

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

ColumnType
track_idint
titlevarchar
release_datedate

Primary key: track_id.

One row per recording.

Table: WriterSplit

ColumnType
track_idint
writer_namevarchar
share_pctint

Primary key (together): track_id, writer_name.

One row per songwriter credited on a track, with their share in whole percent.

Example

The rows must come back in the order shown.

Track

track_idtitlerelease_date
1Monsoon Letters2024-01-12
2Chasing Neon2024-02-02
3Paper Planes2024-03-15
4Golden Hour2024-04-19
5Static Hearts2024-05-24

WriterSplit

track_idwriter_nameshare_pct
1Kavya Rao50
1Vikram Iyer50
2Neha Das40
2Arjun Nair40
3Ishaan Bose60
3Priya Menon30
3Dev Rao20
5Simran Joshi100

Output

track_idtitlewriterstotal_share
2Chasing Neon280
3Paper Planes3110
4Golden Hour00
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: 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

ColumnType
track_idint
titlevarchar
release_datedate

Primary key: track_id.

One row per recording.

Table: WriterSplit

ColumnType
track_idint
writer_namevarchar
share_pctint

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_idtitlerelease_date
1Monsoon Letters2024-01-12
2Chasing Neon2024-02-02
3Paper Planes2024-03-15
4Golden Hour2024-04-19
5Static Hearts2024-05-24

WriterSplit

track_idwriter_nameshare_pct
1Kavya Rao50
1Vikram Iyer50
2Neha Das40
2Arjun Nair40
3Ishaan Bose60
3Priya Menon30
3Dev Rao20
5Simran Joshi100

Output

track_idtitlewriterstotal_share
2Chasing Neon280
3Paper Planes3110
4Golden Hour00

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_id

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

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