Scenario: Brightlane's merchandising team is reviewing which product categories have crossed the $500 revenue threshold this period.
Task: Write a query to return the name, total revenue, and total units sold for each product category that has generated more than $500 in revenue across its line items.
Assumptions:
- A line item's revenue is
quantitymultiplied byunit_price. - The result covers only categories whose total line-item revenue exceeds
$500.
Output:
- One row per qualifying category.
- Columns in this order:
category_name,revenue,units_sold. - Sorted by
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
items_categorized AS (
SELECT
oi.quantity,
oi.unit_price,
c.name AS category_name
FROM
order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
),
category_revenue AS (
SELECT
category_name,
SUM(quantity * unit_price) AS revenue,
SUM(quantity) AS units_sold
FROM
items_categorized
GROUP BY
category_name
),
top_categories AS (
SELECT
category_name,
revenue,
units_sold
FROM
category_revenue
WHERE
revenue > 500
)
SELECT
category_name,
revenue,
units_sold
FROM
top_categories
ORDER BY
revenue DESCThe shape
Three CTEs sequenced as combine, total, threshold. The first joins the three tables that have to come together for a categorised line item to exist, the second rolls those line items up to per-category revenue and units, and the third drops the categories that miss the $500 cut. Each transformation occupies a named layer instead of nesting inside a single statement.
Clause by clause
WITH items_categorized AS (
SELECT oi.quantity, oi.unit_price, c.name AS category_name
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
)Order lines reach their category through the products table, so two joins are needed: order_items to products for the category key, then products to categories for the readable name. The columns kept are just enough for the next layer to compute revenue and unit count.
category_revenue AS (
SELECT category_name, SUM(quantity * unit_price) AS revenue, SUM(quantity) AS units_sold
FROM items_categorized
GROUP BY category_name
)GROUP BY category_name produces one row per category. Both sums run over the already-categorised line items, so each category's totals reflect only its own products. Laptops lead with 21,287 in revenue across 13 units sold.
top_categories AS (
SELECT category_name, revenue, units_sold
FROM category_revenue
WHERE revenue > 500
)The threshold check runs against the rolled-up revenue value, which exists only after the previous layer. Eight categories clear $500; the rest drop out here.
SELECT category_name, revenue, units_sold FROM top_categories ORDER BY revenue DESCreturns the qualifying categories with the highest revenue first.
Why three CTEs instead of one big query
The same answer can be written as a single SELECT with a HAVING clause and a multi-table join all in one expression. The result would match. The reason to split the work into three named layers is that each layer answers a different question: which rows belong to a category, how much each category sold, and which categories cleared the bar. When the report later needs a new threshold or a different join key, the change touches one CTE instead of an entangled monolithic statement.
You practiced sequencing three CTEs — combine, total, threshold — so each transformation occupies its own named layer instead of nesting inside a single statement.
