Top product per category

Each category page gets a “best seller” badge. Find which product earns it.

A product’s units are the sum of order_items.quantity over orders with status = 'delivered'. The best seller in a category is the product with the most units. If two or more products tie for the top, they all get the badge, so return all of them.

Only products with at least one delivered unit can win. A category with no delivered sales has no row.

Return one row per winning product:

column	type	meaning
category	text	the category
product_id	integer	the winning product
name	text	its name
units	integer	its delivered units

Row order doesn’t matter.

Tables: products, order_items, orders.

Approach

Sum delivered units per product, rank products inside each category, keep rank 1.

WITH units AS (
  SELECT p.category, p.product_id, p.name,
         SUM(oi.quantity) AS units
  FROM order_items oi
  JOIN orders   o ON o.order_id   = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status = 'delivered'
  GROUP BY p.category, p.product_id, p.name
),
ranked AS (
  SELECT *,
         RANK() OVER (PARTITION BY category ORDER BY units DESC) AS rnk
  FROM units
)
SELECT category, product_id, name, units
FROM ranked
WHERE rnk = 1;

Why it works

PARTITION BY category restarts the ranking in every category, so each category gets its own rank 1. RANK() gives tied rows the same number, so a two-way tie for first returns two rows.

The filter has to live outside the CTE. SQL computes window functions after WHERE and HAVING, so inside the same SELECT the rank doesn’t exist yet. (DuckDB also offers QUALIFY rnk = 1 as a shortcut for exactly this.)

The other classic answer is to join the per-product units back to MAX(units) per category. It keeps ties too, but reads the units twice.

Under the hood

The window operator hash-partitions the 40 product rows by category, then sorts each partition by units DESC. Ranking is one walk over each sorted partition. Sorting is O(n log n) per partition, which for large partitions is where the time goes. When you only need the top few, a database can use a bounded heap per group instead of a full sort.