Window functions · Medium
How do you return the top two products by revenue within each category?
Short answer
Compute revenue per product, rank products within each category using ROW_NUMBER or DENSE_RANK partitioned by category and ordered by revenue descending, then filter to rank two or less in an outer query. The filter must sit outside, because window functions are evaluated after WHERE. Use DENSE_RANK if tied products should both appear.
Detailed answer
WITH product_revenue AS (
SELECT p.category, p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
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 NOT IN ('cancelled', 'returned')
GROUP BY p.product_id, p.category, p.product_name
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC, product_name) AS rn
FROM product_revenue
)
SELECT category, rn AS rank_in_category, product_name, revenue
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;
Why not WHERE ROW_NUMBER() … <= 2 directly? Window functions are computed in the SELECT phase, after WHERE has already run. Hence the CTE (or subquery). Some warehouses, such as Snowflake and BigQuery, offer QUALIFY to filter on window results in one step; SQLite does not.
Ties: the tie-breaker product_name makes ROW_NUMBER deterministic. If the business says "show everyone tied for second", switch to DENSE_RANK and accept that a category may return more than two rows.