Scenario: Brightlane's finance team needs the combined value of every line item belonging to a shipped order, computed from line-item data rather than any pre-stored total.
Task: Write a query to return the combined value of quantity multiplied by unit_price across every line item belonging to shipped orders.
Assumptions:
- A shipped order has
statusequal to'shipped'. - A line item's value is
quantitymultiplied byunit_price. - The result is a single combined value across every line item belonging to a shipped order.
Output:
- One row, holding the combined shipped line-item value.
- Columns in this order:
total_shipped_item_value.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH
shipped_items AS (
SELECT
oi.quantity * oi.unit_price AS item_value
FROM
order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE
o.status = 'shipped'
)
SELECT
SUM(item_value) AS total_shipped_item_value
FROM
shipped_itemsThe shape
A CTE narrows the rows to shipped-order line items first, then a single SUM over that named result returns the combined value. Splitting the row-narrowing from the totaling is what makes the query read as two clean steps instead of one dense expression.
Clause by clause
WITH shipped_items AS (...)names the intermediate result. Inside the CTE,SELECT oi.quantity * oi.unit_price AS item_valuecomputes each line's value,JOIN orders o ON oi.order_id = o.idbrings the order's status into reach, andWHERE o.status = 'shipped'keeps only line items that belong to a shipped order. The CTE returns one row per qualifying line item, each carrying its value.SELECT SUM(item_value) AS total_shipped_item_value FROM shipped_itemscollapses those rows into the single combined figure of4374.98.
Why this and not a single query
The same total comes out of SELECT SUM(oi.quantity * oi.unit_price) FROM order_items oi JOIN orders o ON oi.order_id = o.id WHERE o.status = 'shipped'. That form is shorter and, for a single-stage aggregation, the leaner choice. The CTE earns its keep when the row-narrowing step is reused or when naming it makes the intent legible. Here the CTE is a stylistic choice; both shapes are correct.
You practiced narrowing the relevant line items in a CTE before producing the single combined total — separating the row-narrowing step from the final calculation.
