Revenue per Category from Items — Join Fanout in SQL

The problem

Scenario: Brightlane's merchandising team wants total revenue by product category, computed from actual line-item sales rather than from any cached total.

Task: Write a query to return each category name and the total revenue generated from line items across all products in that category.

Assumptions:

A line item's revenue is quantity multiplied by unit_price.
The result covers only categories with at least one product appearing on at least one line item.

Output:

One row per qualifying category.
Columns in this order: category_name, revenue.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  c.name AS category_name,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM
  order_items oi
  JOIN products p ON oi.product_id = p.id
  JOIN categories c ON p.category_id = c.id
GROUP BY
  c.name

The shape

The line-item revenue is computed from order_items directly, then rolled up through products and categories for grouping. Because every column in the SUM comes from the leaf table, the chain of joins above it doesn't inflate the aggregation regardless of how many products sit under each category.

Clause by clause

SELECT c.name AS category_name, SUM(oi.quantity * oi.unit_price) AS revenue returns the category name and the combined line-item revenue across every product in that category. The per-line product runs first; the SUM collapses those amounts inside each category group.
FROM order_items oi reads the line items as the leaf of the join chain. Every row contributes its own quantity * unit_price to the total.
JOIN products p ON oi.product_id = p.id attaches the product record to each line item. This is a one-to-one lookup because each line item references one product.
JOIN categories c ON p.category_id = c.id attaches the category record to each product. Also one-to-one — each product has one category.
GROUP BY c.name collapses the joined rows into one row per category, with the SUM running inside each group.

Why this and not summing an order-level revenue column

If the query reached for orders.order_total instead of oi.quantity * oi.unit_price, every line item under an order would carry that same order total, and the SUM would count it once per line item. A three-item order would inflate its order total by three before the category roll-up even started. Summing the leaf-table expression keeps every contributing value distinct, which is the only shape where the category totals come out right.

The trap

The two upstream joins to products and categories look like they could cause fanout, but neither side multiplies — each line item has one product, each product has one category. The danger here is one-to-many in the other direction (one product appears on many line items), and that direction is exactly the shape the SUM needs. Fanout only inflates when the column being aggregated comes from the parent side; here the column comes from the child, so the multiplied rows are the right rows.

You practiced rolling line-item revenue up through two parent layers — product, then category — into one combined value per category.

Return each category name and the total revenue generated from line items across all `products` in that category

The shape

Clause by clause

Why this and not summing an order-level revenue column

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.