Brightlane's finance controller is running a revenue audit and needs line-item data, not order-level totals.
Write a query to return every order line item with its record ID, the order it belongs to, the product ID, the quantity purchased, and the unit price at time of purchase.
Assumptions:
- The
order_itemstable contains one row per product per order — orders with multiple products produce multiple rows here. - Each line item has its own
id, plus anorder_id,product_id,quantity, andunit_price. - The
unit_pricereflects the price at the time the order was placed, which may differ from the product's current price.
Output:
- One row per order line item, with columns
id,order_id,product_id,quantity, andunit_price.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
order_id,
product_id,
quantity,
unit_price
FROM
order_itemsThe shape
FROM order_items is the choice that makes the rest of the query work. The line-item table holds one row per product per order, which is the grain the revenue audit needs; the orders table next to it would only hand back one row per order, no matter how many items each order contained.
Clause by clause
SELECT id, order_id, product_id, quantity, unit_pricereturns five columns in source order. The line-item identifier comes first so each row can be referenced individually, then the foreign keys that tie the line back to its order and its product, then the two columns that actually carry the money: how many were sold, and at what price each.FROM order_itemsis the row source. The table is a junction betweenordersandproducts— every row represents one product appearing on one order, and that's exactly the grain a revenue audit operates at. With noWHEREclause, every line item across every order comes through, which is what the audit needs to reconcile the full books.- The
unit_pricecolumn carries the price as it was at the time of the order, not the product's current price. The prompt calls this out explicitly because it matters: revenue is the price paid, not the price listed today. Reading it fromorder_itemspreserves the historical record; reading the price fromproductswould silently substitute today's price for every old line.
Why this and not FROM orders
The orders table has one row per order. If a single order contained five different products, FROM orders would still return one row for that order, and the line-level detail would be invisible. A revenue audit can't be done at order grain — two orders with the same total can have completely different line-item composition, and the audit has to see that composition to be useful.
The trap
Reading from the table that names the business entity instead of the table that holds the grain you actually need. "Order line item" sounds like it lives on the orders table because the word "order" is in the name. It doesn't. Junction tables like order_items are where many-to-many relationships are recorded, and any question that asks about the relationship itself — which products were on which orders, in what quantity, at what price — needs to read from the junction, not from either side.
You practiced reading from a junction table that records the relationship between two other tables. Many-to-many relationships almost always go through a junction like order_items — read it directly when the line-level detail is the answer.
