Spotify: Explicit Tracks Sitting on Kids Playlists

Easy
923.

Spotify: Explicit Tracks Sitting on Kids Playlists

EasyJoins

MySQL

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

ColumnType
track_idint
titlevarchar
artist_namevarchar
is_explicitbool

Primary key: track_id.

One row per track. is_explicit is 1 when the track carries the explicit-content label.

Table: Playlist

ColumnType
playlist_idint
playlist_namevarchar
is_kidsbool

Primary key: playlist_id.

is_kids is 1 for a playlist published as made for kids.

Table: PlaylistTrack

ColumnType
playlist_idint
track_idint
added_ondate

Primary key (together): playlist_id, track_id.

One row per track on a playlist; a track appears on a playlist at most once.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Track

track_idtitleartist_nameis_explicit
1Monsoon LettersKavya Rao0
2Chasing NeonCoral Static1
3Paper PlanesFirefly Theory0
4Static HeartsBombay Bassline1
5Golden HourLunar Rickshaw1

Playlist

playlist_idplaylist_nameis_kids
10Bedtime Lullabies1
11Road Trip Hits0
12Kids Dance Party1
13Saturday Cartoons1

PlaylistTrack

playlist_idtrack_idadded_on
1012024-05-01
1022024-05-03
1122024-04-11
1142024-04-12
1242024-06-02
1232024-06-02
1222024-06-05
1332024-07-19

Output

playlist_idplaylist_nametrack_idtitle
10Bedtime Lullabies2Chasing Neon
12Kids Dance Party2Chasing Neon
12Kids Dance Party4Static Hearts
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: 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

ColumnType
track_idint
titlevarchar
artist_namevarchar
is_explicitbool

Primary key: track_id.

One row per track. is_explicit is 1 when the track carries the explicit-content label.

Table: Playlist

ColumnType
playlist_idint
playlist_namevarchar
is_kidsbool

Primary key: playlist_id.

is_kids is 1 for a playlist published as made for kids.

Table: PlaylistTrack

ColumnType
playlist_idint
track_idint
added_ondate

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_idtitleartist_nameis_explicit
1Monsoon LettersKavya Rao0
2Chasing NeonCoral Static1
3Paper PlanesFirefly Theory0
4Static HeartsBombay Bassline1
5Golden HourLunar Rickshaw1

Playlist

playlist_idplaylist_nameis_kids
10Bedtime Lullabies1
11Road Trip Hits0
12Kids Dance Party1
13Saturday Cartoons1

PlaylistTrack

playlist_idtrack_idadded_on
1012024-05-01
1022024-05-03
1122024-04-11
1142024-04-12
1242024-06-02
1232024-06-02
1222024-06-05
1332024-07-19

Output

playlist_idplaylist_nametrack_idtitle
10Bedtime Lullabies2Chasing Neon
12Kids Dance Party2Chasing Neon
12Kids Dance Party4Static 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 = 1

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