#0026

Top Product Per Category

MediumSQL20 min15 XP

Problem

Given product sales rows, return the top revenue product in each category.

Top-N per group is a classic analytics interview pattern. GROUP BY alone is not enough because you need the winning row.

sales(category, product, revenue)

Example 1

Input("books", "SQL Guide", 500), ("books", "Python Guide", 700), ("tools", "Keyboard", 300)

Outputbooks/Python Guide/700 and tools/Keyboard/300

Each category is ranked separately, then rank 1 is selected.

CodeSQL

WITH ranked AS (
  SELECT
    -- write your ranking query here
  FROM sales
)
SELECT category, product, revenue
FROM ranked
WHERE rn = 1;

WITH ranked AS (
  SELECT
    -- write your ranking query here
  FROM sales
)
SELECT category, product, revenue
FROM ranked
WHERE rn = 1;

Visible browser tests run here when available.

Testcases1 visible / 3 hidden categories

Two categories

Inputbooks and tools sales

Expectedone product per category

Partitioning prevents products from different categories competing with each other.

Hidden Test Categories

Revenue tieSingle categoryMany products per category