Lesson 27 / 37 · Composing queries

A CTE is a named step that runs first

Everything inside WITH has its own pipeline, shown above the main query. The main query then reads the CTE as if it were a table.

The query

WITH paid AS (
  SELECT *
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, count(*) AS paid_orders
FROM paid
GROUP BY customer_id

Try status <> 'paid' inside the CTE: it is still called paid, but it now hands over the 3 other orders. A name promises nothing.