Most-Streamed Artist in Every Genre

Medium
483.

Most-Streamed Artist in Every Genre

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

ColumnType
artist_idint
artist_namevarchar
genrevarchar

Primary key: artist_id.

One row per artist with their main genre.

Table: Play

ColumnType
play_idint
listener_idint
artist_idint
seconds_playedint

Primary key: play_id.

One row per play; artist_id always names a row of Artist.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Artist

artist_idartist_namegenre
1Prateek KuhadIndie
2Anuv JainIndie
3DivineHip-Hop
4Seedhe MautHip-Hop
5Shreya GhoshalBollywood

Play

play_idlistener_idartist_idseconds_played
1111200
212131
3132240
411229
5143180
615430
7124210
813395
914512

Output

genreartist_namestreams
Hip-HopDivine2
Hip-HopSeedhe Maut2
IndiePrateek Kuhad2
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
artist_idint
artist_namevarchar
genrevarchar

Primary key: artist_id.

One row per artist with their main genre.

Table: Play

ColumnType
play_idint
listener_idint
artist_idint
seconds_playedint

Primary key: play_id.

One row per play; artist_id always names a row of Artist.

Examples

Example 1

Artist

artist_idartist_namegenre
1Prateek KuhadIndie
2Anuv JainIndie
3DivineHip-Hop
4Seedhe MautHip-Hop
5Shreya GhoshalBollywood

Play

play_idlistener_idartist_idseconds_played
1111200
212131
3132240
411229
5143180
615430
7124210
813395
914512

Output

genreartist_namestreams
Hip-HopDivine2
Hip-HopSeedhe Maut2
IndiePrateek Kuhad2

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 = 1

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 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 →