SQL CTEs with WITH
Use common table expressions to name intermediate results, then inspect each stage of a channel revenue report.
Sample query
WITH completed_orders AS (
SELECT channel, total_cents
FROM shop_orders
WHERE status = 'completed'
), channel_totals AS (
SELECT channel, SUM(total_cents) AS revenue_cents
FROM completed_orders
GROUP BY channel
)
SELECT channel, revenue_cents
FROM channel_totals
WHERE revenue_cents >= 11000
ORDER BY revenue_cents DESC, channel;Give each stage one job
A common table expression is a named query that other parts of the same statement can read. The completed_orders CTE answers which rows contribute revenue. The channel_totals CTE summarizes those rows once per channel. The final SELECT applies the report threshold.
Inspect intermediate results
Change the final SELECT to SELECT * FROM channel_totals. You should see 11100 cents for online and 10800 cents for store. Marketplace is absent because its only order in the sample was refunded. If a total looks wrong, select directly from completed_orders to inspect the rows contributing to it.
Apply the final business rule
With the 11000-cent threshold restored, online is the only channel above or at the threshold. Keeping the total calculation separate makes it easy to change the cutoff while leaving the definition of completed revenue in one place.
Query result
| channel | revenue_cents |
|---|---|
| online | 11100 |
