Basic CTEs in PostgreSQL

-- Simple CTE
WITH high_value_customers AS (
  SELECT customer_id, SUM(amount) as total
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 1000
)
SELECT c.name, hvc.total
FROM customers c
JOIN high_value_customers hvc ON c.id = hvc.customer_id;

-- Multiple CTEs
WITH 
  active_users AS (
    SELECT * FROM users WHERE status = 'active'
  ),
  recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
  )
SELECT u.name, COUNT(o.id) as order_count
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY u.name;

-- DETAILED_TAB:
-- CTE with INSERT/UPDATE/DELETE
WITH deleted AS (
  DELETE FROM old_records
  WHERE created_at < NOW() - INTERVAL '1 year'
  RETURNING *
)
INSERT INTO archive_table
SELECT * FROM deleted;

-- Materialized CTE (PostgreSQL 12+)
WITH expensive_calculation AS MATERIALIZED (
  -- This runs once and results are stored
  SELECT complex_function(data) as result
  FROM large_table
)
SELECT * FROM expensive_calculation
WHERE result > 100;