Several steps in one query

You can define several CTEs, separated by commas. Each one can use the ones above it, so a hard question becomes a list of small steps:

WITH order_values AS (
  SELECT order_id, SUM(quantity * unit_price) AS value
  FROM order_items
  GROUP BY order_id
),
big_orders AS (
  SELECT order_id FROM order_values WHERE value >= 500
)
SELECT o.channel, COUNT(*) AS big_orders
FROM big_orders b
JOIN orders o ON o.id = b.order_id
GROUP BY o.channel

Write only one WITH, at the start. After that, it's name AS (…), a comma, the next name AS (…), and finally the main SELECT.

This is how analysts write long reports: one CTE per idea, each one easy to check on its own. While you build it, end with SELECT * FROM the latest step to see what you've got so far.

Other databases: CTEs work the same in DuckDB, PostgreSQL, SQL Server, MySQL 8+ and BigQuery.