Most-Streamed Artist in Every Genre — SQL Problem
On the music app a play counts as a stream only when at least 30 seconds were played.
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #483
Problem statement
On the music app a play counts as a stream only when at least 30 seconds were played. The editorial team wants the top artist of each genre for the year-end playlist covers.
For every genre, return the artist (or artists, if tied) with the most streams, with the columns genre, artist_name and streams. An artist with no streams is never a top artist, so a genre whose artists have no streams does not appear. Return the rows in any order.
Tables
Table: Artist
| Column | Type |
|---|---|
| artist_id | int |
| artist_name | varchar |
| genre | varchar |
Primary key: artist_id.
One row per artist with their main genre.
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| listener_id | int |
| artist_id | int |
| seconds_played | int |
Primary key: play_id.
One row per play; artist_id always names a row of Artist.
Examples
Example 1
Artist
| artist_id | artist_name | genre |
|---|---|---|
| 1 | Prateek Kuhad | Indie |
| 2 | Anuv Jain | Indie |
| 3 | Divine | Hip-Hop |
| 4 | Seedhe Maut | Hip-Hop |
| 5 | Shreya Ghoshal | Bollywood |
Play
| play_id | listener_id | artist_id | seconds_played |
|---|---|---|---|
| 1 | 11 | 1 | 200 |
| 2 | 12 | 1 | 31 |
| 3 | 13 | 2 | 240 |
| 4 | 11 | 2 | 29 |
| 5 | 14 | 3 | 180 |
| 6 | 15 | 4 | 30 |
| 7 | 12 | 4 | 210 |
| 8 | 13 | 3 | 95 |
| 9 | 14 | 5 | 12 |
Output
| genre | artist_name | streams |
|---|---|---|
| Hip-Hop | Divine | 2 |
| Hip-Hop | Seedhe Maut | 2 |
| Indie | Prateek Kuhad | 2 |
How to solve Most-Streamed Artist in Every Genre
Two steps. First aggregate: join plays to artists, drop plays shorter than 30 seconds in WHERE, and COUNT(*) per artist. Group by artist_id (plus the columns you select), not by name — two artists could share a name.
Then pick the maximum within each genre. RANK() OVER (PARTITION BY genre ORDER BY COUNT(*) DESC) can be computed in the same query as the aggregate, because window functions run after GROUP BY; the outer query keeps rnk = 1, which includes every artist tied for first. ROW_NUMBER would silently drop one of the tied artists.
Without windows, compare each artist's count with the genre's MAX — either through a correlated subquery or by joining a per-genre maximum back on (genre, count). Artists with zero streams never reach the aggregate (the inner join finds no qualifying play), so a genre with only silent artists has no row. Cost: one pass over plays and a sort per genre.
Reference solution (MySQL)
WITH streams AS (
SELECT a.genre, a.artist_name, COUNT(*) AS streams,
RANK() OVER (PARTITION BY a.genre ORDER BY COUNT(*) DESC) AS rnk
FROM Play p
JOIN Artist a ON a.artist_id = p.artist_id
WHERE p.seconds_played >= 30
GROUP BY a.artist_id, a.genre, a.artist_name
)
SELECT genre, artist_name, streams FROM streams WHERE rnk = 1Another way
WITH s AS (SELECT a.artist_id, a.genre, a.artist_name, COUNT(*) AS streams FROM Artist a JOIN Play p ON p.artist_id = a.artist_id AND p.seconds_played > 29 GROUP BY a.artist_id, a.genre, a.artist_name)
SELECT genre, artist_name, streams FROM s WHERE streams = (SELECT MAX(s2.streams) FROM s s2 WHERE s2.genre = s.genre)Another way
WITH s AS (SELECT a.artist_id, a.genre, a.artist_name, COUNT(*) AS streams FROM Artist a JOIN Play p ON p.artist_id = a.artist_id WHERE p.seconds_played >= 30 GROUP BY a.artist_id, a.genre, a.artist_name)
SELECT s.genre, s.artist_name, s.streams FROM s JOIN (SELECT genre, MAX(streams) AS best FROM s GROUP BY genre) b ON b.genre = s.genre AND b.best = s.streams← Completion Rate of Every Series · Artist Royalties for May 2024 →