SQL interview question: Counts and Totals Across Joined Tables
Most Prolific Artists
The editorial team wants to feature artists ranked by their catalog size.
For each artist, show their album count, total tracks, and total play time in minutes.
Return artist_name, album_count, track_count, and total_minutes (rounded to 1 decimal). Order by track_count descending.
Skills: multiple aggregates with GROUP BY across JOINs
Tables
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 |
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 |
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 |