Scenario: Brightlane's revenue analyst is investigating whether the total_amount stored on each order matches the combined value of its line items. The diagnostic places the recorded total alongside the line-item-derived total per order, so any discrepancies are visible inline.
Task: Write a query to return each order's id, the recorded_total stored on the order, the item_count of its line items, and the item_total computed from those line items.
Assumptions:
- A line item's value is
quantitymultiplied byunit_price. - An order's
item_countis the count of its line items; itsitem_totalis the combined line-item value across all of its line items. - The result covers only
orderswith at least one line item on record.
Output:
- One row per order with at least one line item.
- Columns in this order:
order_id,recorded_total,item_count,item_total. - Sorted by
item_countdescending.
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
item_counts AS (
SELECT
order_id,
COUNT(*) AS item_count,
SUM(quantity * unit_price) AS item_total
FROM
order_items
GROUP BY
order_id
)
SELECT
o.id AS order_id,
o.total_amount AS recorded_total,
ic.item_count,
ic.item_total
FROM
orders o
JOIN item_counts ic ON ic.order_id = o.id
ORDER BY
ic.item_count DESCThe shape
Aggregate the line items per order in a CTE first, then join that one-row-per-order summary back to orders so the recorded total and the line-item-derived total sit on the same row. The recorded value and the derived value are now in the same place, ready to be eyeballed for discrepancies without a second query.
Clause by clause
WITH item_counts AS (SELECT order_id, COUNT(*) AS item_count, SUM(quantity * unit_price) AS item_total FROM order_items GROUP BY order_id)collapses the many line items per order into one row per order. Both the count and the dollar total are computed in this CTE, so the next layer joins one-to-one rather than one-to-many.SELECT o.id AS order_id, o.total_amount AS recorded_total, ic.item_count, ic.item_totalreturns four columns per order: the order id, the value stored on the order, and the two derived metrics from the CTE. Placing them in this order reads as "what we recorded vs. what the line items actually add up to."FROM orders o JOIN item_counts ic ON ic.order_id = o.idpairs each order with its one summary row. The inner join drops orders with no line items, which is what the prompt asks for.ORDER BY ic.item_count DESCputs the multi-item orders first. Those are the orders where a fanout-driven discrepancy would show up most loudly. The reference shows order 64 with three items, a recorded total of 1999, and an item_total of 2497.99 — a gap that an inline side-by-side display surfaces immediately.
The trap
Skipping the CTE and joining orders directly to order_items is where this problem turns into the inflated-aggregate class. SUM(oi.quantity * oi.unit_price) over that joined result is correct per group, but o.total_amount is the same value per order repeated once per line item — pull it through a sum without grouping by order and it multiplies. Pre-aggregating in the CTE collapses the many side to one row first, so when orders joins to item_counts the relationship is one-to-one and every parent column behaves the way the analyst expects.
You practiced placing recorded totals next to derived totals so the discrepancy per order is visible inline — a side-by-side diagnostic that surfaces which orders disagree without a second comparison query.
