Spotify: Explicit Tracks Sitting on Kids Playlists
The trust and safety team at Spotify audits playlists marked as made for kids: an explicit track should never sit on one, and every hit is sent to the…
- Difficulty: Easy
- Topics: Joins
- Dialect: MySQL
- Problem: #923
Problem statement
The trust and safety team at Spotify audits playlists marked as made for kids: an explicit track should never sit on one, and every hit is sent to the curator to fix.
Return one row for every explicit track on a kids playlist, with the columns playlist_id, playlist_name, track_id and title. Explicit tracks on other playlists, and clean tracks on kids playlists, are fine. Return the rows in any order.
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 |
| artist_name | varchar |
| is_explicit | bool |
Primary key: track_id.
One row per track. is_explicit is 1 when the track carries the explicit-content label.
Table: Playlist
| Column | Type |
|---|---|
| playlist_id | int |
| playlist_name | varchar |
| is_kids | bool |
Primary key: playlist_id.
is_kids is 1 for a playlist published as made for kids.
Table: PlaylistTrack
| Column | Type |
|---|---|
| playlist_id | int |
| track_id | int |
| added_on | date |
Primary key: playlist_id, track_id.
One row per track on a playlist; a track appears on a playlist at most once.
Examples
Example 1
Track
| track_id | title | artist_name | is_explicit |
|---|---|---|---|
| 1 | Monsoon Letters | Kavya Rao | 0 |
| 2 | Chasing Neon | Coral Static | 1 |
| 3 | Paper Planes | Firefly Theory | 0 |
| 4 | Static Hearts | Bombay Bassline | 1 |
| 5 | Golden Hour | Lunar Rickshaw | 1 |
Playlist
| playlist_id | playlist_name | is_kids |
|---|---|---|
| 10 | Bedtime Lullabies | 1 |
| 11 | Road Trip Hits | 0 |
| 12 | Kids Dance Party | 1 |
| 13 | Saturday Cartoons | 1 |
PlaylistTrack
| playlist_id | track_id | added_on |
|---|---|---|
| 10 | 1 | 2024-05-01 |
| 10 | 2 | 2024-05-03 |
| 11 | 2 | 2024-04-11 |
| 11 | 4 | 2024-04-12 |
| 12 | 4 | 2024-06-02 |
| 12 | 3 | 2024-06-02 |
| 12 | 2 | 2024-06-05 |
| 13 | 3 | 2024-07-19 |
Output
| playlist_id | playlist_name | track_id | title |
|---|---|---|---|
| 10 | Bedtime Lullabies | 2 | Chasing Neon |
| 12 | Kids Dance Party | 2 | Chasing Neon |
| 12 | Kids Dance Party | 4 | Static Hearts |
How to solve Spotify: Explicit Tracks Sitting on Kids Playlists
Playlists and tracks are a many-to-many relation: a track sits on many playlists and a playlist holds many tracks. The bridge table PlaylistTrack stores the pairs, so the answer is a walk across it — join each pair to its playlist and to its track, then keep the pairs where the playlist is for kids and the track is explicit.
Both conditions are row-level filters after the joins, so WHERE p.is_kids = 1 AND t.is_explicit = 1 is all it takes. A track that is explicit but only on adult playlists fails the first test; a clean song on a kids playlist fails the second. Because the primary key of the bridge is (playlist_id, track_id), each offending pair appears exactly once and no DISTINCT is needed.
The alternatives start from the other side — every kids playlist crossed with every explicit track, kept when the pair exists in the bridge (EXISTS) — or filter each table first in derived tables and then join. With indexes on the bridge's key the join version reads each link once, which is the cheapest plan for a catalogue-sized table.
Reference solution (MySQL)
SELECT p.playlist_id, p.playlist_name, t.track_id, t.title
FROM PlaylistTrack pt
JOIN Playlist p ON p.playlist_id = pt.playlist_id
JOIN Track t ON t.track_id = pt.track_id
WHERE p.is_kids = 1 AND t.is_explicit = 1Another way
SELECT p.playlist_id, p.playlist_name, t.track_id, t.title FROM Playlist p, Track t WHERE p.is_kids = 1 AND t.is_explicit = 1 AND EXISTS (SELECT 1 FROM PlaylistTrack pt WHERE pt.playlist_id = p.playlist_id AND pt.track_id = t.track_id)Another way
SELECT p.playlist_id, p.playlist_name, t.track_id, t.title FROM (SELECT * FROM Playlist WHERE is_kids <> 0) p JOIN PlaylistTrack pt ON pt.playlist_id = p.playlist_id JOIN (SELECT * FROM Track WHERE is_explicit <> 0) t ON t.track_id = pt.track_id← Razorpay: Subscription Billing Cycles With No Charge Attempt · Spotify: Long-Form Podcast Episodes Released in 2024 →