sql cte common-table-expressions recursive postgresql

Common Table Expressions (CTEs) for readable queries

Maria Garcia Feb 2026

2 tabs

-- Basic CTE
WITH high_value_customers AS (
  SELECT
    user_id,
    SUM(total) as lifetime_value
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT
  u.name,
  u.email,
  hvc.lifetime_value
FROM users u
INNER JOIN high_value_customers hvc ON u.id = hvc.user_id
ORDER BY hvc.lifetime_value DESC;

-- Multiple CTEs
WITH
  monthly_sales AS (
    SELECT
      DATE_TRUNC('month', order_date) as month,
      SUM(total) as total_sales
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
  ),
  monthly_costs AS (
    SELECT
      DATE_TRUNC('month', expense_date) as month,
      SUM(amount) as total_costs
    FROM expenses
    GROUP BY DATE_TRUNC('month', expense_date)
  )
SELECT
  COALESCE(s.month, c.month) as month,
  COALESCE(s.total_sales, 0) as sales,
  COALESCE(c.total_costs, 0) as costs,
  COALESCE(s.total_sales, 0) - COALESCE(c.total_costs, 0) as profit
FROM monthly_sales s
FULL OUTER JOIN monthly_costs c ON s.month = c.month
ORDER BY month;

-- CTEs referencing other CTEs
WITH
  active_users AS (
    SELECT id, name, email
    FROM users
    WHERE last_login > CURRENT_DATE - INTERVAL '30 days'
  ),
  user_orders AS (
    SELECT
      au.id,
      au.name,
      COUNT(o.id) as order_count
    FROM active_users au
    LEFT JOIN orders o ON au.id = o.user_id
    GROUP BY au.id, au.name
  )
SELECT *
FROM user_orders
WHERE order_count > 0
ORDER BY order_count DESC;

-- CTE for data transformation pipeline
WITH
  cleaned_data AS (
    SELECT
      TRIM(LOWER(email)) as email,
      name,
      created_at
    FROM raw_users
    WHERE email IS NOT NULL
  ),
  deduped_data AS (
    SELECT DISTINCT ON (email)
      email,
      name,
      created_at
    FROM cleaned_data
    ORDER BY email, created_at DESC
  )
SELECT * FROM deduped_data;

-- Recursive CTE: Organization hierarchy
WITH RECURSIVE org_chart AS (
  -- Base case: Top-level managers
  SELECT
    id,
    name,
    manager_id,
    1 as level,
    name as path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: Employees with managers
  SELECT
    e.id,
    e.name,
    e.manager_id,
    oc.level + 1,
    oc.path || ' > ' || e.name
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  REPEAT('  ', level - 1) || name as org_structure,
  level,
  path
FROM org_chart
ORDER BY path;

-- Generate series (date range)
WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' as date

  UNION ALL

  SELECT date + INTERVAL '1 day'
  FROM date_series
  WHERE date < DATE '2024-12-31'
)
SELECT
  ds.date,
  COALESCE(SUM(o.total), 0) as daily_revenue
FROM date_series ds
LEFT JOIN orders o ON DATE(o.created_at) = ds.date
GROUP BY ds.date
ORDER BY ds.date;

-- Category tree traversal
WITH RECURSIVE category_tree AS (
  -- Root categories
  SELECT
    id,
    name,
    parent_id,
    1 as depth,
    ARRAY[id] as path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Child categories
  SELECT
    c.id,
    c.name,
    c.parent_id,
    ct.depth + 1,
    ct.path || c.id
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE NOT c.id = ANY(ct.path)  -- Prevent cycles
)
SELECT
  REPEAT('  ', depth - 1) || name as category_hierarchy,
  depth,
  path
FROM category_tree
ORDER BY path;

-- Find all subordinates
WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 5  -- Starting employee

  UNION ALL

  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT name, id
FROM subordinates;

-- Materialized CTE (PostgreSQL 12+)
WITH monthly_aggregates AS MATERIALIZED (
  SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as total_revenue
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT * FROM monthly_aggregates
WHERE total_revenue > 10000;

2 files · sql Explain with highlit

CTEs improve query readability and maintainability. WITH clauses define named subqueries referenced in main query. I use CTEs to break complex queries into logical steps. Recursive CTEs handle hierarchical data—org charts, category trees, graph traversal. Multiple CTEs can reference each other. CTEs are query-scoped temporary result sets. They're more readable than nested subqueries. Some databases materialize CTEs, others inline them. MATERIALIZED hint forces materialization in PostgreSQL. CTEs excel for code clarity but may impact performance versus derived tables. Understanding when to use CTEs versus subqueries optimizes both readability and speed. CTEs are essential for complex data analysis.