Scenario: Brightlane's buying team is evaluating which products have generated the most revenue across all order activity.
Task: Write a query to return each product's id, name, and total revenue earned from its line items in order_items.
Assumptions:
- A line item's revenue is
quantitymultiplied byunit_price. - The result covers only products that appear on at least one order line.
Output:
- One row per product with at least one line item on record.
- Columns in this order:
product_id,product_name,total_revenue. - Sorted by
total_revenuedescending.
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_revenue AS (
SELECT
oi.product_id,
p.name AS product_name,
oi.quantity * oi.unit_price AS line_revenue
FROM
order_items oi
JOIN products p ON oi.product_id = p.id
),
product_totals AS (
SELECT
product_id,
product_name,
SUM(line_revenue) AS total_revenue
FROM
item_revenue
GROUP BY
product_id,
product_name
)
SELECT
product_id,
product_name,
total_revenue
FROM
product_totals
ORDER BY
total_revenue DESCThe shape
Two CTEs, with the per-line revenue derived once in the first and rolled up once in the second. Computing quantity * unit_price inside the join layer means every downstream reference to line revenue points at the same expression in the same place. The aggregation in the second layer just sums what is already there.
Clause by clause
WITH item_revenue AS (
SELECT oi.product_id, p.name AS product_name, oi.quantity * oi.unit_price AS line_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
)The join attaches each line item to its product name, and quantity * unit_price AS line_revenue derives the row-level revenue right here. This is the only place the multiplication is written, which is the architectural point.
product_totals AS (
SELECT product_id, product_name, SUM(line_revenue) AS total_revenue
FROM item_revenue
GROUP BY product_id, product_name
)GROUP BY product_id, product_name collapses the line items per product, and SUM(line_revenue) adds the already-computed line revenues. Crest Pro 14" tops the list at 11,994, Sofa 3-Seater follows at 7,990.
SELECT product_id, product_name, total_revenue FROM product_totals ORDER BY total_revenue DESCreturns the per-product totals sorted from largest to smallest.
You practiced staging the per-line revenue derivation in one CTE before per-product totals in another, so the line-revenue computation lives in a single named layer.
