SQLCheat Sheet

SQL CTEs Cheat Sheet

Quick-reference for Common Table Expressions: WITH syntax, chaining, recursion, and performance considerations. Each section includes copy-ready snippets with inline output comments.

Basic CTE Syntax

A CTE (Common Table Expression) is a named temporary result set defined with WITH. It exists only for the duration of the query.

Simple CTE

WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE is_active = true
)
SELECT * FROM active_users
ORDER BY name;

CTE with aggregation

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT month, revenue
FROM monthly_revenue
WHERE revenue > 10000
ORDER BY month;

Chaining Multiple CTEs

Define multiple CTEs with a single WITH keyword, separated by commas. Each CTE can reference earlier ones.

Two-step transformation

WITH orders_2024 AS (
  SELECT *
  FROM orders
  WHERE created_at >= '2024-01-01'
    AND created_at < '2025-01-01'
),
high_value AS (
  SELECT *
  FROM orders_2024
  WHERE amount > 1000
)
SELECT customer_id, COUNT(*) AS big_orders
FROM high_value
GROUP BY customer_id;

Three CTEs building on each other

WITH raw_events AS (
  SELECT user_id, event_type, created_at
  FROM events
  WHERE created_at >= '2024-01-01'
),
signups AS (
  SELECT user_id, MIN(created_at) AS signup_date
  FROM raw_events
  WHERE event_type = 'signup'
  GROUP BY user_id
),
purchases AS (
  SELECT user_id, COUNT(*) AS purchase_count
  FROM raw_events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  s.user_id,
  s.signup_date,
  COALESCE(p.purchase_count, 0) AS purchases
FROM signups s
LEFT JOIN purchases p ON p.user_id = s.user_id;

Recursive CTEs

WITH RECURSIVE defines a CTE that references itself. It requires a base case (anchor) and a recursive step joined with UNION ALL.

Category hierarchy (tree traversal)

WITH RECURSIVE category_tree AS (
  -- Base case: top-level categories
  SELECT id, name, parent_id, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive step: children
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree
ORDER BY depth, name;

Employee org chart

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id,
    org.path || ' > ' || e.name
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT * FROM org;

Generate a number series

-- Generate numbers 1 to 10
WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

Always include a termination condition (WHERE n < 10) to prevent infinite loops.

CTE vs Subquery

CTEs and subqueries are often interchangeable. CTEs win on readability; subqueries win on simplicity for one-off transformations.

Subquery version

SELECT country, avg_spend
FROM (
  SELECT country, AVG(total_spend) AS avg_spend
  FROM customers
  GROUP BY country
) t
WHERE avg_spend > 1000;

CTE version (same result, more readable)

WITH country_spending AS (
  SELECT country, AVG(total_spend) AS avg_spend
  FROM customers
  GROUP BY country
)
SELECT country, avg_spend
FROM country_spending
WHERE avg_spend > 1000;

CTEs are clearer when the derived table is referenced multiple times or the query has many steps.

Referencing a CTE Multiple Times

Unlike subqueries, a CTE can be referenced multiple times in the same query.

Self-join on a CTE

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
  curr.month,
  curr.revenue AS current_revenue,
  prev.revenue AS prev_month_revenue,
  curr.revenue - prev.revenue AS change
FROM monthly curr
LEFT JOIN monthly prev
  ON prev.month = curr.month - INTERVAL '1 month';

With a subquery, you would have to repeat the entire aggregation twice.

Materialized vs Inline CTEs

PostgreSQL 12+ inlines most CTEs like subqueries. Use MATERIALIZED to force a separate evaluation.

Force materialization

-- PostgreSQL 12+: CTE is inlined by default
-- Use MATERIALIZED to force separate evaluation
WITH expensive_calc AS MATERIALIZED (
  SELECT id, heavy_function(data) AS result
  FROM big_table
)
SELECT * FROM expensive_calc WHERE result > 100;

Force inlining

-- Explicitly tell PostgreSQL to inline the CTE
WITH filtered AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM filtered WHERE amount > 1000;
-- Optimizer can push the amount > 1000 filter down

MATERIALIZED/NOT MATERIALIZED are PostgreSQL-specific. Other databases have different CTE optimization behavior.

CTE for Multi-Step Transformations

Break complex business logic into named, testable steps. Each CTE is independently verifiable.

Funnel analysis pipeline

WITH signups AS (
  SELECT user_id, MIN(created_at) AS signup_date
  FROM events
  WHERE event_type = 'signup'
  GROUP BY user_id
),
first_purchase AS (
  SELECT user_id, MIN(created_at) AS purchase_date
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
),
funnel AS (
  SELECT
    s.user_id,
    s.signup_date,
    fp.purchase_date,
    fp.purchase_date - s.signup_date AS days_to_convert
  FROM signups s
  LEFT JOIN first_purchase fp ON fp.user_id = s.user_id
)
SELECT
  COUNT(*) AS total_signups,
  COUNT(purchase_date) AS converted,
  ROUND(100.0 * COUNT(purchase_date) / COUNT(*), 2) AS conversion_pct,
  AVG(days_to_convert) AS avg_days_to_convert
FROM funnel;

CTE with DML (INSERT, UPDATE, DELETE)

PostgreSQL supports CTEs in DML statements. Use this to chain data modifications or return results from mutations.

DELETE with CTE and RETURNING

-- Archive and delete old orders in one statement
WITH deleted AS (
  DELETE FROM orders
  WHERE created_at < '2023-01-01'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM deleted;

Writable CTEs (INSERT/UPDATE/DELETE in WITH) are PostgreSQL-specific.

Upsert with CTE

-- Compute values in CTE, then upsert
WITH new_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM recent_orders
  GROUP BY customer_id
)
INSERT INTO customer_stats (customer_id, total_spent)
SELECT customer_id, total FROM new_totals
ON CONFLICT (customer_id) DO UPDATE
SET total_spent = customer_stats.total_spent + EXCLUDED.total_spent;

Recursive CTE Patterns

Common patterns beyond simple tree traversal.

Generate date series

-- Generate one row per day for a date range
WITH RECURSIVE dates AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM dates
  WHERE day < '2024-01-31'
)
SELECT day FROM dates;

PostgreSQL has generate_series() for this — recursive CTE is the portable approach.

Path accumulation (breadcrumbs)

WITH RECURSIVE breadcrumbs AS (
  SELECT id, name, parent_id,
    ARRAY[name] AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
    b.path || c.name
  FROM categories c
  JOIN breadcrumbs b ON c.parent_id = b.id
)
SELECT id, name,
  array_to_string(path, ' > ') AS breadcrumb
FROM breadcrumbs;

Learn SQL in Depth

SQL Subqueries Practice →SQL Window Functions Practice →SQL Aggregation Practice →

Warm-up1 / 2

Can you write this from memory?

Create a CTE named 'active_users' that selects all users where status = 'active', then select all columns from it.

Start Practicing SQL

Free daily exercises with spaced repetition. No credit card required.

← Back to SQL Syntax Practice