×

sql · level 3 · unit 1 · lesson 2

  1. meet it
  2. guess first
  3. your turn
  4. fix it
  5. nailed it

Tap a green word if you get lost.

meet it

Chain CTEs with commas to build a .

One WITH can hold several steps. Write WITH once, then separate the steps with commas. Each step can read any step above it, so the query becomes a pipeline: every stage takes the last result and changes it a little.

Here, spend adds up each customer's orders. average works out the mean of those totals. The main query keeps the customers who beat it.

Build pipelines one step at a time. Write the first step, run SELECT * FROM it, check the rows, then add the next. Bugs have nowhere to hide.

WITH spend AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  GROUP BY customer_id
),
average AS (
  SELECT AVG(spent) AS avg_spent FROM spend
)
SELECT customer_id, spent
FROM spend
WHERE spent > (SELECT avg_spent FROM average)
ORDER BY spent DESC;

The average here is 61, so only customers 2 and 3 make the cut.