Revenue by category

Finance wants one number per category: how much money delivered orders brought in.

Revenue for a line item is quantity * unit_price. Only count orders whose status is 'delivered'. Cancelled, returned and still-shipping orders don’t count yet.

Return one row per category with:

column	type	meaning
category	text	the product category
revenue	decimal	total delivered revenue, rounded to 2 decimals

Row order doesn’t matter.

Tables: orders, order_items, products.

Approach

Join the line items to their order (for the status) and their product (for the category), keep only delivered orders, then group.

SELECT p.category,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) 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 = 'delivered'
GROUP BY p.category;

Why it works

WHERE runs before GROUP BY, so undelivered line items never reach the sums. Use unit_price from order_items, not price from products: the line item records what the customer actually paid, discount included.

Under the hood

DuckDB builds a hash table keyed by category and updates one running sum per key as rows stream past. Five categories means five entries, however many line items there are. That’s the whole cost of a GROUP BY with few groups: one pass and a tiny hash map.