SQL interview question: Longest Item per Group

Each Artist's Longest Song

The playlist curation team wants to find each artist's longest track for a "Deep Cuts" playlist.

Return artist_name, track_title, and duration_seconds for each artist's longest track. Order by duration descending.

Skills: JOIN across three tables with GROUP BY

Tables

tracks 40 rows
  • track_id integer PK
  • title text
  • album_id integer → albums
  • duration_seconds integer
track_idtitlealbum_idduration_seconds
1Come Together1259
2Something1182
3Here Comes the Sun1185
albums 18 rows
  • album_id integer PK
  • title text
  • artist_id integer → artists
  • release_year integer
album_idtitleartist_idrelease_year
1Abbey Road11969
2Sgt. Pepper's Lonely Hearts Club Band11967
3The White Album11968
artists 10 rows
  • artist_id integer PK
  • name text
  • genre text
artist_idnamegenre
1The BeatlesRock
2Pink FloydProgressive Rock
3Michael JacksonPop
About the music sample database

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app

Keep going