SQL Challenger
All SQL guides

SQL ROW_NUMBER with PARTITION BY

Use ROW_NUMBER and PARTITION BY to find the two best-selling albums in each genre.

Sample query

WITH album_sales AS (
  SELECT a.id, a.genre, a.title, COALESCE(SUM(s.quantity), 0) AS copies_sold
  FROM albums AS a
  LEFT JOIN sales AS s ON s.album_id = a.id
  GROUP BY a.id, a.genre, a.title
), ranked AS (
  SELECT genre, title, copies_sold,
    ROW_NUMBER() OVER (
      PARTITION BY genre
      ORDER BY copies_sold DESC, title, id
    ) AS position
  FROM album_sales
)
SELECT genre, position, title, copies_sold
FROM ranked
WHERE position <= 2
ORDER BY genre, position;

Rank albums after combining their sales

To pick two albums per genre for a featured display, first calculate one sales total per album. The album_sales query does that before any ranking takes place. Ranking the original sales rows would give albums with several transactions several chances to take a spot.

Restart the numbering for every genre

PARTITION BY genre tells ROW_NUMBER to start at one again when the genre changes. Inside each genre, sort by copies sold from highest to lowest. Use title and ID to break ties consistently. The outer query keeps positions one and two after those numbers have been assigned.

Check genres with fewer than two albums

Night Bus and Blue Hour take the two Jazz spots. Ambient has just one album, so Low Tide is the only Ambient row. Because the first query uses LEFT JOIN, it can include albums with no sales. If the display should feature only albums that sold at least one copy, filter copies_sold before assigning positions.

Query result

genrepositiontitlecopies_sold
Ambient1Low Tide0
Electronic1Soft Circuit0
Indie1Static Bloom3
Indie2Coastline1
Jazz1Night Bus5
Jazz2Blue Hour2
Try a related challenge