Stretch#12 · Window functions

Best seller in each category

For each category, return the category name, the product that sold the most units (product) and its units. Count units from all non-cancelled orders. If two products tie, pick the one whose name comes first alphabetically. Sort by category name.

Write your query in the lab. It runs in your browser and tells you exactly what's wrong if it doesn't match.

Solve it in the lab

Hints

Hint 1

First total the units per product, then rank inside each category.

Hint 2

ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC, product), then keep rank 1.

Expected output

Your result should match this, in this order (6 rows).

name	product	units
Accessories	Adjustable Laptop Stand	27
Laptops	Fieldbook 13 Rugged Laptop	13
Monitors	ClearView 24" Monitor	12
Networking	Cat6 Ethernet Cable 10m	31
Peripherals	HD 1080p Webcam	33
Storage	Pocket 256GB USB-C Drive	33

Model answer

Show the solution

WITH units AS (
  SELECT c.name AS category, p.name AS product, SUM(oi.quantity) AS units
  FROM categories c
  JOIN products p     ON p.category_id = c.category_id
  JOIN order_items oi ON oi.product_id = p.product_id
  JOIN orders o       ON o.order_id = oi.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY c.name, p.name
), ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC, product) AS rn
  FROM units
)
SELECT category AS name, product, units
FROM ranked
WHERE rn = 1
ORDER BY name;

Other queries that return the same rows are correct too. The lab checks your result, not your exact wording.