SQL Challenger
All SQL guides

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

channelrevenue_cents
online11100
Try a related challenge