SQL interview question: Rows Above Their Group Average
Above-Average Track Length per Album
The music team wants to identify unusually long tracks on each album.
Find tracks that are longer than the average track length on their album.
Return album_title, track_title, duration (in seconds), and album_avg_duration. Use a correlated subquery or window function. Show the 10 longest (duration descending).
Skills: correlated subquery for row-wise comparison
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 |