CTEs con WITH

Las CTEs (Common Table Expressions) son subconsultas con nombre definidas antes de la consulta principal usando WITH. Hacen el código más legible y permiten reutilizar resultados intermedios. Las CTEs recursivas son perfectas para datos jerárquicos como árboles de categorías.

-- CTE básica: más legible que subconsulta en FROM
WITH ventas_mensuales AS (
  SELECT
    DATE_TRUNC('month', fecha) AS mes,
    SUM(total) AS ingresos
  FROM orders
  WHERE fecha >= '2024-01-01'
  GROUP BY DATE_TRUNC('month', fecha)
)
SELECT
  mes,
  ingresos,
  LAG(ingresos) OVER (ORDER BY mes) AS mes_anterior,
  ROUND((ingresos - LAG(ingresos) OVER (ORDER BY mes)) * 100.0
        / LAG(ingresos) OVER (ORDER BY mes), 2) AS crecimiento_pct
FROM ventas_mensuales
ORDER BY mes;

-- Múltiples CTEs encadenadas
WITH
clientes_activos AS (
  SELECT DISTINCT user_id
  FROM orders
  WHERE fecha >= NOW() - INTERVAL '90 days'
),
resumen_clientes AS (
  SELECT
    u.id,
    u.nombre,
    COUNT(o.id) AS total_pedidos,
    SUM(o.total) AS gasto_total
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE u.id IN (SELECT user_id FROM clientes_activos)
  GROUP BY u.id, u.nombre
)
SELECT
  nombre,
  total_pedidos,
  gasto_total,
  ROUND(gasto_total / total_pedidos, 2) AS ticket_promedio
FROM resumen_clientes
ORDER BY gasto_total DESC
LIMIT 10;

-- CTE recursiva: árbol de categorías
-- Tabla: categorias(id, nombre, padre_id)
WITH RECURSIVE arbol_categorias AS (
  -- Caso base: categorías raíz (sin padre)
  SELECT id, nombre, padre_id, 0 AS nivel,
         nombre::TEXT AS ruta
  FROM categorias
  WHERE padre_id IS NULL

  UNION ALL

  -- Caso recursivo: hijos de cada categoría
  SELECT c.id, c.nombre, c.padre_id,
         ac.nivel + 1,
         ac.ruta || ' > ' || c.nombre
  FROM categorias c
  INNER JOIN arbol_categorias ac ON ac.id = c.padre_id
)
SELECT REPEAT('  ', nivel) || nombre AS categoria_indentada,
       ruta
FROM arbol_categorias
ORDER BY ruta;