Common Table Expressions & Recursive Queries

Common Table Expressions (CTEs)

CTEs use the WITH clause to define temporary named result sets. They make complex queries readable by breaking them into logical steps. You can chain multiple CTEs in one query.

-- Break a complex query into readable steps
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE deleted_at IS NULL
    AND last_login_at > NOW() - INTERVAL '30 days'
),
user_orders AS (
  SELECT
    u.id AS user_id,
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
  FROM active_users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.created_at > NOW() - INTERVAL '90 days'
  GROUP BY u.id, u.name
)
SELECT name, order_count, total_spent
FROM user_orders
WHERE total_spent > 500
ORDER BY total_spent DESC;

Recursive CTEs for Hierarchies

Recursive CTEs have two parts: a base case (non-recursive term) and a recursive term that references the CTE itself. They are essential for tree and graph structures.

-- Organization chart: find all reports under a manager
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INT REFERENCES employees(id)
);

WITH RECURSIVE org_tree AS (
  -- Base case: start with the CEO (no manager)
  SELECT id, name, manager_id, 0 AS depth, name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports of current level
  SELECT e.id, e.name, e.manager_id, t.depth + 1,
         t.path || ' > ' || e.name
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT depth, path, name FROM org_tree ORDER BY path;

Category Trees & File Systems

-- Nested categories (e.g., e-commerce)
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id INT REFERENCES categories(id)
);

-- Get full category breadcrumb path
WITH RECURSIVE cat_path AS (
  SELECT id, name, parent_id, ARRAY[name] AS breadcrumb
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, cp.breadcrumb || c.name
  FROM categories c
  JOIN cat_path cp ON c.parent_id = cp.id
)
SELECT id, name, array_to_string(breadcrumb, ' / ') AS full_path
FROM cat_path
ORDER BY full_path;

Graph Queries: Shortest Path

-- Find all connections in a social graph (with cycle detection)
CREATE TABLE friendships (
  user_a INT NOT NULL,
  user_b INT NOT NULL
);

WITH RECURSIVE connections AS (
  SELECT user_b AS person, 1 AS degree, ARRAY[user_a, user_b] AS path
  FROM friendships
  WHERE user_a = 1

  UNION ALL

  SELECT f.user_b, c.degree + 1, c.path || f.user_b
  FROM friendships f
  JOIN connections c ON f.user_a = c.person
  WHERE f.user_b != ALL(c.path)  -- prevent cycles
    AND c.degree < 3              -- limit depth
)
SELECT DISTINCT person, degree FROM connections ORDER BY degree;

Generating Series with Recursive CTEs

-- Generate a date series (can also use generate_series)
WITH RECURSIVE dates AS (
  SELECT DATE '2025-01-01' AS day
  UNION ALL
  SELECT day + 1 FROM dates WHERE day < '2025-01-31'
)
SELECT d.day, COALESCE(COUNT(o.id), 0) AS orders
FROM dates d
LEFT JOIN orders o ON DATE(o.created_at) = d.day
GROUP BY d.day
ORDER BY d.day;