Common Table Expressions (WITH)

-- Simple CTE
WITH active_users AS (
  SELECT * 
  FROM users 
  WHERE status = 'active'
)
SELECT * 
FROM active_users 
WHERE country = 'US';

-- 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.id, u.name;

-- CTE with aggregation
WITH user_stats AS (
  SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
)
SELECT 
  u.name,
  us.order_count,
  us.total_spent
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE us.total_spent > 1000;

-- Recursive CTE (organizational hierarchy)
WITH RECURSIVE org_chart AS (
  -- Base case: top-level employees
  SELECT 
    id,
    name,
    manager_id,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive case: employees under managers
  SELECT 
    e.id,
    e.name,
    e.manager_id,
    oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

-- Recursive CTE (category tree)
WITH RECURSIVE category_tree AS (
  SELECT 
    id,
    name,
    parent_id,
    CAST(name AS VARCHAR(1000)) AS path
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT 
    c.id,
    c.name,
    c.parent_id,
    CONCAT(ct.path, ' > ', c.name)
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

-- CTE for date series
WITH RECURSIVE dates AS (
  SELECT CAST('2024-01-01' AS DATE) AS date
  UNION ALL
  SELECT date + INTERVAL '1 day'
  FROM dates
  WHERE date < '2024-12-31'
)
SELECT 
  d.date,
  COUNT(o.id) AS orders
FROM dates d
LEFT JOIN orders o ON DATE(o.created_at) = d.date
GROUP BY d.date;

-- Multiple CTEs with references
WITH 
  monthly_revenue AS (
    SELECT 
      DATE_TRUNC('month', created_at) AS month,
      SUM(total) AS revenue
    FROM orders
    GROUP BY month
  ),
  revenue_growth AS (
    SELECT 
      month,
      revenue,
      LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
      revenue - LAG(revenue) OVER (ORDER BY month) AS growth
    FROM monthly_revenue
  )
SELECT 
  month,
  revenue,
  prev_month_revenue,
  growth,
  ROUND(growth / prev_month_revenue * 100, 2) AS growth_percentage
FROM revenue_growth;