DocsAdvancedCommon Table Expressions

Common Table Expressions

Advanced

Concept

A Common Table Expression (CTE) is a named, temporary result set defined at the top of a query using the WITH keyword. It exists only for the duration of that single query and can be referenced like a table in the main SELECT, INSERT, UPDATE, or DELETE statement that follows.

CTEs improve readability by breaking complex queries into named, logical steps. Instead of deeply nested subqueries, you define each intermediate result as a CTE with a meaningful name and then compose them in the final query. This makes queries significantly easier to write, debug, and maintain.

Multiple CTEs can be defined in a single WITH clause, separated by commas. Later CTEs can reference earlier ones, enabling step-by-step data transformations. CTEs are not materialized by default in most databases (they are inlined like views), but PostgreSQL offers MATERIALIZED / NOT MATERIALIZED hints for controlling this behavior.

Syntax

-- Single CTE
WITH active_customers AS (
  SELECT customer_id, first_name, last_name
  FROM customers
  WHERE status = 'active'
)
SELECT * FROM active_customers;

-- Multiple CTEs
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
),
avg_revenue AS (
  SELECT AVG(revenue) AS avg_rev
  FROM monthly_revenue
)
SELECT
  mr.month,
  mr.revenue,
  ar.avg_rev,
  mr.revenue - ar.avg_rev AS diff
FROM monthly_revenue mr
CROSS JOIN avg_revenue ar
ORDER BY mr.month;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Multi-step analysis: top customers and their favorite categories
WITH customer_spend AS (
  SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.order_id) AS order_count
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.first_name, c.last_name
),
top_customers AS (
  SELECT * FROM customer_spend
  WHERE total_spent > 1000
),
customer_categories AS (
  SELECT
    o.customer_id,
    p.category,
    SUM(oi.quantity) AS items_bought,
    ROW_NUMBER() OVER (
      PARTITION BY o.customer_id
      ORDER BY SUM(oi.quantity) DESC
    ) AS rn
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY o.customer_id, p.category
)
SELECT
  tc.customer,
  tc.total_spent,
  tc.order_count,
  cc.category AS favorite_category,
  cc.items_bought
FROM top_customers tc
JOIN customer_categories cc
  ON cc.customer_id = tc.customer_id AND cc.rn = 1
ORDER BY tc.total_spent DESC;

Common Pitfalls & Tips

1CTEs are not automatically materialized. If a CTE is referenced multiple times and is expensive to compute, it may run multiple times. Check your database's behavior or use MATERIALIZED hints.
2CTEs cannot be indexed. If you need indexed intermediate results, consider a temporary table instead.
3Do not overuse CTEs for trivial transformations — they add visual overhead without benefit if the logic is simple enough for a single query.

Practice CTE queries