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_id | title | album_id | duration_seconds |
|---|---|---|---|
| 1 | Come Together | 1 | 259 |
| 2 | Something | 1 | 182 |
| 3 | Here Comes the Sun | 1 | 185 |
albums 18 rows
- album_id integer PK
- title text
- artist_id integer → artists
- release_year integer
| album_id | title | artist_id | release_year |
|---|---|---|---|
| 1 | Abbey Road | 1 | 1969 |
| 2 | Sgt. Pepper's Lonely Hearts Club Band | 1 | 1967 |
| 3 | The White Album | 1 | 1968 |
artists 10 rows
- artist_id integer PK
- name text
- genre text
| artist_id | name | genre |
|---|---|---|
| 1 | The Beatles | Rock |
| 2 | Pink Floyd | Progressive Rock |
| 3 | Michael Jackson | Pop |