Item Revenue per Customer — Join Fanout in SQL

The problem

Scenario: Brightlane's revenue team needs total spending per customer computed from individual line-item data, as a cross-check against any order-level totals.

Task: Write a query to return each customer's customer_id and their total revenue across all line items in their orders.

Assumptions:

A line item's revenue is quantity multiplied by unit_price.
A customer's total revenue is the combined revenue across every line item in every order they have placed.
The result covers only customers who have placed at least one order containing line items.

Output:

One row per qualifying customer.
Columns in this order: customer_id, total_item_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
  o.customer_id,
  SUM(oi.quantity * oi.unit_price) AS total_item_revenue
FROM
  orders o
  JOIN order_items oi ON oi.order_id = o.id
GROUP BY
  o.customer_id

The shape

Every column being summed lives on order_items — the child side of the join — so the line-item-per-row shape is exactly the shape the aggregation needs. SUM(quantity * unit_price) grouped by customer_id rolls those per-line amounts straight up to the customer total without any inflation.

Clause by clause

SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_item_revenue returns each customer's ID and the combined revenue across every line item in every order they placed. The multiplication runs per line item; the SUM then totals those amounts within each customer group.
FROM orders o reads the orders, carrying the customer_id that the customer-level grouping needs. Each order joins to its line items in the next clause.
JOIN order_items oi ON oi.order_id = o.id pairs each order with its line items. The join multiplies orders by line items — an order with three line items appears three times in the joined result — but that's the correct shape because the values being summed are per-line-item.
GROUP BY o.customer_id collapses the joined rows down to one per customer, with the SUM running inside each group.

Why this is safe even though the join fans out

The fanout trap fires when an order-level column gets summed over a row-multiplied result. Here, quantity * unit_price comes from order_items — the same side that's doing the multiplying — so each row contributes its own distinct value. If the query summed o.order_total (an order-level column) instead, an order with three line items would have its total counted three times. Knowing which side of the join each summed column comes from is what makes the difference between a correct total and an inflated one.

You practiced totaling line-item amounts across two parent levels — line item up to order, then order up to customer — into one combined value per customer.

Return each customer's `customer_id` and their total revenue across all line items in their `orders`

The shape

Clause by clause

Why this is safe even though the join fans out

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