ReviseAlgo Logo

Common Table Expressions (CTE)

WITH Clause (CTEs)

Declaring temporary logical query view blocks.

Last Updated: June 12, 2026 • 14 min read

A Common Table Expression (CTE) is a named, temporary result set defined with the WITH clause. Think of it as giving a name to a subquery so you can reference it in the main query. CTEs make complex SQL readable by breaking it into logical, named steps instead of deeply nested subqueries.

1. Introduction

The WITH clause lets you define one or more CTEs before the main SELECT, INSERT, UPDATE, or DELETE. Each CTE acts like a temporary table that exists only for the duration of the query. Multiple CTEs can reference each other in sequence, creating a readable pipeline of transformations — a pattern that replaces convoluted nested subqueries.

2. Why It Matters

  • Readability: Named steps are self-documenting. WITH monthly_sales AS (...) is clearer than a nested subquery.
  • Reusability: Define a CTE once, reference it multiple times in the same query — no repeated subquery code.
  • Debugging: Run each CTE independently to verify intermediate results before combining them.
  • Required for recursion: Recursive CTEs (WITH RECURSIVE) are the only way to traverse hierarchical data of arbitrary depth in SQL.

3. Real-World Analogy

Imagine writing a complex recipe. Instead of one massive paragraph, you break it into steps: "First, make the sauce. Second, prepare the dough. Third, assemble and bake." Each step has a name and builds on the previous one. CTEs do the same for SQL — each named step transforms data, and the final query combines them into the result.

4. How It Works

  • Basic syntax: WITH cte_name AS (SELECT ...) SELECT * FROM cte_name.
  • Multiple CTEs: Separate with commas. Later CTEs can reference earlier ones: WITH a AS (...), b AS (SELECT ... FROM a) SELECT * FROM b.
  • Scope: CTEs exist only for the statement that follows them. They don't persist across queries.
  • Materialization: PostgreSQL 12+ can inline CTEs (optimize them like subqueries). Use MATERIALIZED to force evaluation or NOT MATERIALIZED to force inlining.

5. Internal Architecture

6. Visual Explanation

7. Practical Example

Department payroll report with multiple CTEs:

Replacing nested subqueries with CTEs:

8. Common Mistakes

CTE referenced before definition

CTEs must be defined in dependency order. CTE b cannot reference CTE c if c is defined after b. They execute top-to-bottom.

Forgetting MATERIALIZED hint for expensive CTEs

PostgreSQL 12+ may inline CTEs by default. If a CTE is referenced multiple times and is expensive, add MATERIALIZED to prevent re-evaluation.

Interview Insight

"Can a CTE reference itself?" — Not without the RECURSIVE keyword. A plain CTE that references itself throws an error. WITH RECURSIVE enables self-referencing for tree traversal and hierarchy queries.

9. Quick Quiz

Q1: Can you use a CTE with INSERT, UPDATE, or DELETE statements?

Answer: Yes. CTEs work with any DML statement. Example: WITH to_archive AS (SELECT id FROM orders WHERE created_at < NOW() - INTERVAL '2 years') DELETE FROM orders WHERE id IN (SELECT id FROM to_archive).

Q2: What happens if two CTEs have the same name?

Answer: PostgreSQL throws an error: duplicate CTE names are not allowed within the same WITH clause. Each CTE must have a unique name.

10. Scenario-Based Challenge

Challenge: Sales Funnel Analysis

Build a CTE pipeline that analyzes an e-commerce sales funnel: visitors → sign-ups → first purchase → repeat purchase.

CTEs needed:

  1. visitors — distinct users who visited the site.
  2. signups — visitors who created an account.
  3. first_purchase — signups who placed their first order.
  4. repeat_buyers — first-purchasers with 2+ orders.

Final query: show conversion rates at each stage (e.g., 40% of visitors sign up, 25% of signups purchase).

11. Debugging Exercise

Find the bug in this CTE query:

Issues:

  1. Invalid multi-table reference: The stats CTE tries to reference high_earners twice in one FROM clause with a comma (cross join). Fix: use two separate aggregates in one SELECT: SELECT AVG(salary), COUNT(*) FROM high_earners.
  2. Comma instead of semicolon: FROM high_earners, with a trailing comma causes a syntax error before COUNT.

12. Interview Questions

Q1: What's the difference between a CTE and a subquery?

A: Functionally similar, but CTEs are named, reusable within the query, and improve readability. CTEs can also be recursive. Performance-wise, PostgreSQL 12+ can inline CTEs like subqueries unless MATERIALIZED is specified.

Q2: When should you use MATERIALIZED vs NOT MATERIALIZED?

A: MATERIALIZED forces PostgreSQL to evaluate the CTE once and cache it — useful when referenced multiple times. NOT MATERIALIZED lets the planner inline it — useful when the CTE is referenced once and the planner can push down predicates for better performance.

Q3: Can CTEs be used in views?

A: Yes. CTEs work inside CREATE VIEW definitions. This is a common pattern for building complex views that break logic into named, readable steps.

13. Production Considerations

  • Use MATERIALIZED for expensive, multi-referenced CTEs: Prevents redundant evaluation when the same CTE is joined multiple times.
  • Prefer CTEs over nested subqueries for readability: Named steps are self-documenting and easier to debug in production query logs.
  • Check EXPLAIN plans: PostgreSQL 12+ inlines CTEs by default (good). But for complex queries, verify the plan doesn't re-evaluate CTEs unnecessarily.
  • CTEs in data-modifying statements: CTEs with INSERT/UPDATE/DELETE are powerful for multi-step data operations. Use RETURNING in modifying CTEs to pass results to subsequent CTEs.
  • Avoid deeply nested CTE chains: More than 5-6 CTEs in a single query becomes hard to maintain. Consider splitting into views or application-level steps.