CTEs

What you will be able to do

• Rewrite a nested query as a CTE
• Chain multiple CTEs
• Use CTEs for step-wise filters before aggregates

Why this matters for data work

CTEs (WITH name AS (SELECT …)) name intermediate results. Pipelines become a story: clean → join → aggregate — the same mental model as Python transforms, in SQL.

Concepts

• WITH c AS (SELECT …) SELECT … FROM c
• Multiple CTEs separated by commas.
• CTEs are usually optimization fences in spirit for humans; engines may inline them.
• Prefer CTE over deep subquery nesting for team SQL.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
WITH completed AS (
  SELECT *
  FROM orders
  WHERE status = 'completed'
),
with_region AS (
  SELECT c.*, s.region, s.store_name
  FROM completed c
  JOIN stores s ON c.store_id = s.store_id
)
SELECT region, count(*) AS n, round(sum(line_total), 2) AS revenue
FROM with_region
GROUP BY region
ORDER BY revenue DESC;

WITH daily AS (
  SELECT order_ts::date AS d, sum(line_total) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT d, revenue
FROM daily
ORDER BY d DESC
LIMIT 7;
SQL

What you should see

• Region revenue from a two-step CTE.
• Recent days of completed revenue.

Common mistakes

• Comma errors between CTEs.
• Referencing a CTE before it is defined.
• Huge CTE chains that should be tables/views in production.

Next

Window basics