CTE (WITH): hacer legibles las consultas complejas

Estructura tus consultas en pasos con nombre usando WITH y encadena CTE como un pipeline de transformaciones.

7 min · 3 preguntas

Una subconsulta anidada tres niveles de profundidad se lee de dentro hacia fuera: ilegible e imposible de retomar seis meses después. La CTE (Common Table Expression, la cláusula WITH) invierte el orden de lectura: nombras cada paso intermedio igual que nombrarías una función, y la consulta se lee de arriba abajo. Carga este conjunto de datos en sqliteonline.com o sqlite3.

CREATE TABLE payments (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  paid_at TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO payments (customer, paid_at, amount)
VALUES
  ('alice', '2026-01-10', 120.0),
  ('bruno', '2026-01-22', 80.0),
  ('alice', '2026-02-05', 60.0),
  ('chloe', '2026-02-18', 200.0),
  ('bruno', '2026-03-03', 40.0),
  ('alice', '2026-03-15', 90.0),
  ('chloe', '2026-03-28', 150.0),
  ('bruno', '2026-04-09', 300.0);

Sintaxis básica: WITH name AS (SELECT ...) seguida de la consulta principal, que usa name como una tabla común. Aquí calculamos los ingresos mensuales extrayendo el mes de la fecha con substr (las fechas de SQLite son texto ISO 8601, una elección del motor más que un estándar).

WITH monthly AS (
  SELECT substr(paid_at, 1, 7) AS month,
         SUM(amount) AS revenue
  FROM payments
  GROUP BY month
)
SELECT * FROM monthly ORDER BY month;

El verdadero poder viene del encadenamiento: un solo WITH, varias CTE separadas por comas, cada una capaz de leer las definidas antes de ella. Construyes un pipeline: agregar, calcular una estadística, comparar. Aquí buscamos los meses cuyos ingresos superan el promedio mensual (se espera: 2026-03 y 2026-04, siendo el promedio 260).

WITH monthly AS (
  SELECT substr(paid_at, 1, 7) AS month,
         SUM(amount) AS revenue
  FROM payments
  GROUP BY month
),
stats AS (
  SELECT AVG(revenue) AS avg_revenue
  FROM monthly
)
SELECT m.month, m.revenue,
       ROUND(s.avg_revenue, 1) AS avg_all
FROM monthly AS m
CROSS JOIN stats AS s
WHERE m.revenue > s.avg_revenue
ORDER BY m.month;

Prueba de conocimientos

Comprueba que has retenido los puntos clave de esta lección.

1. ¿Cómo se declaran varias CTE en la misma consulta?
   • Una palabra clave WITH antes de cada CTE
   • Un solo WITH, las CTE separadas por comas
   • Anidándolas una dentro de otra
2. ¿Puede una CTE leer otra CTE del mismo WITH?
   • No, cada CTE está aislada
   • Sí, pero solo las definidas antes de ella
   • Sí, en cualquier orden
3. ¿Cuál es la principal ventaja de una CTE frente a una subconsulta anidada?
   • Siempre es más rápida
   • Crea una tabla permanente y reutilizable
   • Da nombre a cada paso y hace que el flujo sea legible y comprobable