Revenue per Order from Items — Join Fanout in SQL

The problem

Scenario: Brightlane's fulfillment team needs each order's value computed from its individual line items, as a check against any order-level revenue figures stored separately.

Task: Write a query to return each order's id and the total revenue across its line items in order_items.

Assumptions:

A line item's revenue is quantity multiplied by unit_price.
An order's item_revenue is the combined revenue across all of its line items.
The result covers only orders with at least one line item on record.

Output:

One row per order with at least one line item.
Columns in this order: order_id, 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
  oi.order_id,
  SUM(oi.quantity * oi.unit_price) AS item_revenue
FROM
  order_items oi
  JOIN orders o ON oi.order_id = o.id
GROUP BY
  oi.order_id

The shape

The revenue is computed from order_items directly, so SUM(quantity * unit_price) grouped by order_id produces one revenue figure per order with no inflation risk. Every value being summed comes from the same row in the same child table, which is the only shape where the sum lands correctly.

Clause by clause

SELECT oi.order_id, SUM(oi.quantity * oi.unit_price) AS item_revenue returns each order's ID and the combined revenue across its line items. The multiplication runs per line item; the SUM then totals those per-line amounts inside each group.
FROM order_items oi reads the line items. This is the child table, and every row contributes one product of quantity and unit_price to the running total.
JOIN orders o ON oi.order_id = o.id restricts the line items to those whose parent order actually exists in orders. Without a matching order, a line item drops out of the result, which matches the prompt's "covers only orders with at least one line item" constraint.
GROUP BY oi.order_id collapses the line items into one row per order. The SUM runs inside each group, totaling the per-line revenues for that order.

Why this shape is safe from fanout

Every column being summed lives on order_items — the same side of the join that's being multiplied. There is no order-level column entering the SUM, so the join's one-row-per-line-item shape is exactly the right shape for the aggregation. Fanout only inflates results when the column being summed comes from the parent side, which isn't the case here.

You practiced computing per-parent totals from child rows — combining line-item amounts up to a single value per order.

Return each order's `id` and the total revenue across its line items in `order_items`

The shape

Clause by clause

Why this shape is safe from fanout

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