PostgreSQL CTEs and Recursive Queries for Hierarchical Data

-- CTE replaces a subquery with a named, readable block
WITH active_users AS (
  SELECT id, email FROM users WHERE status = 'active'
),
recent_orders AS (
  SELECT user_id, count(*) AS order_count
  FROM orders
  WHERE created_at > now() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.email, o.order_count
FROM active_users u
JOIN recent_orders o ON o.user_id = u.id
ORDER BY o.order_count DESC;

-- MATERIALIZED: CTE executes once, result stored — optimization fence
WITH MATERIALIZED expensive_calc AS (
  SELECT user_id, heavy_computation() AS result FROM large_table
)
SELECT * FROM expensive_calc WHERE result > 100;

-- NOT MATERIALIZED: planner can inline and optimize (default in PG12+)
WITH NOT MATERIALIZED recent AS (
  SELECT * FROM orders WHERE created_at > now() - INTERVAL '1 day'
)
SELECT * FROM recent WHERE status = 'pending';

CREATE TABLE categories (
  id        INT PRIMARY KEY,
  name      TEXT,
  parent_id INT REFERENCES categories(id)
);

-- Get full category path for each node
WITH RECURSIVE category_path AS (
  -- Base case: top-level categories
  SELECT id, name, parent_id, name::TEXT AS path, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive case: children
  SELECT c.id, c.name, c.parent_id,
         cp.path || ' > ' || c.name,
         cp.depth + 1
  FROM categories c
  JOIN category_path cp ON cp.id = c.parent_id
)
SELECT id, name, path, depth
FROM category_path
ORDER BY path;

-- Find all connections within 3 hops
WITH RECURSIVE connections AS (
  SELECT user_id, friend_id, 1 AS hops
  FROM friendships
  WHERE user_id = 42

  UNION

  SELECT f.user_id, f.friend_id, c.hops + 1
  FROM friendships f
  JOIN connections c ON c.friend_id = f.user_id
  WHERE c.hops < 3
)
SELECT DISTINCT friend_id, min(hops) AS min_hops
FROM connections
GROUP BY friend_id
ORDER BY min_hops;