CTEs (WITH)

Overview

A Common Table Expression (CTE) is a named, temporary result set defined with the WITH keyword. It exists only for the duration of the query that follows it. Think of it as giving a name to a subquery so you can reference it like a table. WITH paid_orders AS ( SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE status = 'paid' GROUP BY customer_id ) SELECT c.name, po.order_count FROM customers c JOIN paid_orders po ON po.customer_id = c.id ORDER BY c.name; The CTE 'paid_orders' computes a summary (customer_id + count of paid orders), then the main query joins it to customers. Result: Alice (2), Bob (1), Carlos (1), Emma (1), Farah (1). You can chain multiple CTEs separated by commas, and later CTEs can reference earlier ones: WITH step1 AS (...), step2 AS (SELECT ... FROM step1 ...) SELECT ... FROM step2 ...; Mental model: CTEs are named building blocks. Each one transforms a row-set, and you snap them together top-to-bottom, with the final SELECT assembling the answer.

CTEs make complex queries readable by breaking them into named, sequential steps instead of deeply nested subqueries. They are easier to debug (you can run each CTE independently), easier to maintain, and a natural fit for analytics pipelines where each step builds on the last.

Where used: multi-step analytics reports, breaking complex dashboard queries into readable stages, reusing the same derived table multiple times in one query

Why learn this

• Replace deeply nested subqueries with named, sequential steps that are easy to read, test, and modify
• Build multi-step analytics pipelines in a single SQL statement — a pattern used in every reporting tool and BI dashboard

Query walkthrough

WITH paid_orders AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.name, po.order_count
FROM customers c
JOIN paid_orders po ON po.customer_id = c.id
ORDER BY c.name;

Focus: Watch the CTE build a summary table (customer_id + count of paid orders), then see the main query join it to customers — two clean, named steps instead of a nested subquery.

Common mistakes

• Treating CTEs as materialized tables: In Postgres 12+, non-recursive CTEs are inlined by the optimizer by default (like subqueries). They are NOT guaranteed to execute only once or be materialized. If you need materialization, add MATERIALIZED after the CTE name. Don't assume a CTE improves performance — it's primarily a readability tool.
• Referencing a CTE that is defined later: CTEs are processed top-to-bottom. A CTE can only reference CTEs defined BEFORE it in the WITH list, not after. Reorder your CTE definitions so dependencies come first.
• Forgetting that CTEs are scoped to one statement: A CTE only lives for the single SELECT/INSERT/UPDATE/DELETE that follows the WITH block. You cannot reference it in a later, separate statement. If you need persistence, use a temporary table or view.

Glossary

inlined

A performance trick where the database replaces a reference to a calculation directly with the calculation itself to speed things up.

materialized

When a database temporarily saves the actual results of a complex query to memory or disk so it does not have to recalculate them.

optimizer

The part of the database system that figures out the fastest and most efficient way to execute your query.

Recall questions

• What is a CTE and how do you define one?
• Can a CTE reference another CTE defined in the same WITH block?
• How does a CTE differ from a subquery in terms of readability?

Understanding checks

Which customers appear and what are their counts?

Alice (2), Bob (2)

The CTE counts orders per customer: Alice has 2 (orders 1,2), Bob has 2 (orders 3,8), Carlos 1, Diana 1, Emma 1, Farah 1. The WHERE oc.cnt >= 2 keeps only Alice and Bob.

What names does this return?

Alice, Bob, Carlos, Emma, Farah

The first CTE filters orders to paid ones (orders 1,2,4,6,7,8 — customer_ids 1,1,3,5,6,2). The second CTE joins to customers and deduplicates with DISTINCT. The final SELECT returns the 5 names alphabetically.

Practice tasks

Refactor a nested subquery into a CTE

This query uses an inline subquery. Refactor it to use a CTE named 'expensive_products' for the subquery, keeping the same result and ORDER BY.

Challenge

Two-step CTE: category revenue

Using two chained CTEs, compute the total revenue per product category (price × quantity from order_items joined to products), then in the final SELECT return only categories with total revenue above 100. Order by total revenue descending. Name the CTEs 'item_revenue' and 'category_totals'.

Continue learning

Previous: Scalar & IN Subqueries

Next: Recursive CTEs

Return to SQL Roadmap