Common Table Expressions (CTE)

Introduction

A CTE (WITH … AS (…)) is a named, in-query temporary result. CTEs make complex queries readable by breaking them into named steps. Postgres also supports recursive CTEs for tree/graph traversal.

Understanding the topic

Core concepts to understand:

• WITH paid AS (SELECT ... FROM orders WHERE status='paid') SELECT ... FROM paid;
• Multiple CTEs separated by commas.
• Recursive CTE: WITH RECURSIVE tree AS (...).
• Treat them as named subqueries — cleaner and reusable.
• Modern optimizers inline non-recursive CTEs.

Syntax reference

Visual workflow / architecture:

WITH
  paid AS (
    SELECT * FROM orders WHERE status = 'paid'
  ),
  totals AS (
    SELECT user_id, sum(amount) AS spend FROM paid GROUP BY user_id
  )
SELECT u.email, t.spend
FROM   users u JOIN totals t ON t.user_id = u.id
WHERE  t.spend > 1000;

Real-world use

Analytics, reporting and ETL queries are almost always written as CTE pipelines for clarity. Recursive CTEs power org charts, threaded comments and graph traversal.

Best practices

• Refactor any query > 30 lines into CTEs.
• Name CTEs after the meaning, not the table.
• Use recursive CTEs for trees with care (depth limits).

Hands-on exercise

Interview preparation — practice these questions:

• Q1. CTE vs subquery.
• Q2. Show a recursive CTE.
• Q3. Are CTEs always materialized?
• Q4. Multiple CTEs syntax.
• Q5. When to use a CTE over a temp table.