Nivel 3 · Valor de vida del cliente

Una expresión de tabla común (CTE) es una subconsulta con nombre que defines al inicio con WITH y luego usas como una tabla:

WITH order_revenue AS (
  SELECT o.order_id, o.customer_id,
         SUM(i.quantity * i.unit_price * (1 - i.discount_pct / 100.0)) AS revenue
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.status = 'completed' AND i.quantity > 0
  GROUP BY o.order_id, o.customer_id
)
SELECT customer_id, COUNT(*) AS orders, SUM(revenue) AS lifetime_revenue
FROM order_revenue
GROUP BY customer_id;

Hace el mismo trabajo que una tabla derivada en FROM, pero se lee de arriba abajo, como explicarías la lógica en voz alta: “primero obtén los ingresos por pedido, luego súmalos por cliente”.

Por qué el valor promedio de pedido necesita dos pasos

El valor promedio de pedido (AOV) es el promedio de los totales de pedido. Si calcularas AVG(quantity * unit_price ...) directamente sobre las líneas unidas, obtendrías el valor promedio de línea, un número mucho más pequeño. El CTE fija el grano en una fila por pedido, así que AVG(revenue) es un AOV real. Esto es lo más útil que hacen los CTE para los analistas: hacer explícito el grano de cada paso.

Varios CTE

Sepáralos con comas. Los posteriores pueden leer a los anteriores:

WITH order_revenue AS (
  ...
),
customer_value AS (
  SELECT customer_id, SUM(revenue) AS lifetime_revenue
  FROM order_revenue
  GROUP BY customer_id
)
SELECT *
FROM customer_value
WHERE lifetime_revenue > 10000;

Pon a los CTE el nombre de lo que representa una fila (order_revenue, customer_value, monthly_sales). Así cualquiera puede leer el plan de la consulta solo con los nombres.

Depurar con CTE

Mientras desarrollas, termina la consulta con SELECT * FROM order_revenue LIMIT 20 para mirar cualquier paso de forma aislada. Eso hace el código inicial. Comprueba el conteo de filas de cada paso con lo que esperas (aquí, una fila por pedido completado) antes de construir sobre él.

Notas por dialecto

Los CTE se admiten en Postgres, DuckDB, BigQuery, Snowflake, SQL Server y MySQL 8+ (no en MySQL 5.7). Postgres antes de la versión 12 siempre materializaba los CTE, lo que podía hacerlos más lentos que las subconsultas; en las versiones actuales el optimizador los integra en línea, como hacen DuckDB y BigQuery. WITH RECURSIVE es una función aparte para recorrer jerarquías.

Nota de entrevista

En las entrevistas de SQL en vivo, estructurar tu respuesta con CTE casi siempre es buena idea. Muestra tu forma de pensar, facilita que el entrevistador te siga y te permite probar cada paso. Una consulta larga con tres niveles de subconsultas anidadas también sirve, pero es mucho más difícil de explicar bajo presión.

Errores comunes

• Promediar valores de línea y llamarlo AOV. AVG(quantity * unit_price ...) sobre las líneas unidas es el promedio de línea. Suma primero cada pedido en un CTE y luego toma AVG(revenue).
• Escribir WITH dos veces. WITH a AS (...) WITH b AS (...) es un error de sintaxis. Usa un solo WITH y separa los CTE con comas: WITH a AS (...), b AS (...).
• Seleccionar una columna por la que el CTE no agrupó. Si order_revenue agrupa solo por order_id, o.customer_id no puede aparecer en su SELECT. Agrupa por o.order_id, o.customer_id.
• Dejar la línea de depuración. SELECT * FROM order_revenue LIMIT 20 sirve para mirar un paso; la consulta final debe resumir el CTE a una fila por cliente.

En SQL Server

WITH funciona igual, pero la sentencia anterior debe terminar con punto y coma, por eso los scripts suelen empezar con ;WITH. Para echar un vistazo a un paso, usa SELECT TOP 20 * FROM order_revenue.