Spotify's playlist team is studying how evenly collaborative playlists are built.
A playlist is collaborative only if it has at least 2 distinct non-NULL contributors after ownership is assigned. Exclude playlists with fewer than 2 human contributors.
2
NULL
Each distinct track_id in a playlist has exactly one owner. Select the row with the earliest added_at for that playlist_name and track_id. If multiple rows share that earliest timestamp, assign ownership to the alphabetically first non-NULL contributor; sort NULL after all usernames. Algorithmic tracks whose selected owner is NULL count toward total distinct tracks but not toward any contributor.
track_id
added_at
playlist_name
contributor
Include only collaborative playlists with at least 3 distinct tracks. For each, return its top contributor: the human contributor owning the most distinct tracks. Break ties by contributor ascending. Calculate concentration_ratio as top_contributor_track_count / total_distinct_track_count, rounded to exactly 2 decimal places.
3
concentration_ratio
top_contributor_track_count / total_distinct_track_count
Return playlist_name, top_contributor, and concentration_ratio, in that order. Sort by the rounded concentration_ratio descending, then playlist_name ascending.
top_contributor
playlist_contributions
INT
VARCHAR(255)
TIMESTAMP NOT NULL
101
Indie Chill Mix
mara_b
2024-01-05 10:00:00
102
2024-01-06 11:00:00
103
jordan_k
2024-01-07 09:00:00
104
2024-01-08 08:00:00
105
Sunday Coffee Loop
priya_t
2024-02-01 07:30:00
Indie Chill Mix has 4 distinct tracks and 2 human contributors. mara_b owns tracks 101 and 102, while jordan_k owns track 103. Track 104 is algorithmic, so it only contributes to the denominator. mara_b therefore has a concentration ratio of 2/4 = 0.50. Sunday Coffee Loop has one human contributor, so it is excluded.
4
2/4 = 0.50
DROP TABLE IF EXISTS playlist_contributions; CREATE TABLE playlist_contributions ( track_id INT NOT NULL, playlist_name VARCHAR(255) NOT NULL, contributor VARCHAR(255), added_at TIMESTAMP NOT NULL ); INSERT INTO playlist_contributions VALUES (101, 'Indie Chill Mix', 'mara_b', '2024-01-05 10:00:00'), (102, 'Indie Chill Mix', 'mara_b', '2024-01-06 11:00:00'), (103, 'Indie Chill Mix', 'jordan_k', '2024-01-07 09:00:00'), (104, 'Indie Chill Mix', NULL, '2024-01-08 08:00:00'), (105, 'Sunday Coffee Loop', 'priya_t', '2024-02-01 07:30:00');