Nombra tus pasos con CTE

Por qué esta lección está aquí y no más adelante

La mayoría de los cursos dejan las expresiones de tabla comunes para un módulo avanzado. Este las enseña ahora, porque los agentes escriben SQL lleno de CTE. Un estudiante que no puede leer una cadena de cinco CTE no puede auditar nada de lo que produzca un agente. Leer va antes que escribir, y esto es lo que leerás.

Una expresión de tabla común, escrita con WITH, es una consulta con nombre a la que puedes referirte más adelante. Encadenarlas permite construir un resultado paso a paso, de modo que cada paso tenga un nombre que indique qué produce.

Esta es una pregunta al estilo de la lección anterior - ingresos por país, excluyendo pedidos cancelados - escrita como la haría la mayoría al principio, en un solo bloque:

SELECT s.country_name, SUM(o.order_total) AS revenue
FROM orders o
JOIN stores s ON o.store_id = s.store_id
WHERE o.order_status IS DISTINCT FROM 'cancelled'
GROUP BY s.country_name
ORDER BY revenue DESC;

Funciona, pero todo ocurre a la vez: el filtro, la unión y la agregación están en una sola instrucción, y para revisarla debes mantener las tres cosas en la cabeza al mismo tiempo. Ahora la misma consulta dividida en pasos: filtrar los pedidos, unir la tienda y luego agregar. Cada CTE recibe el nombre de su salida.

WITH order_level AS (
  SELECT order_id, store_id, order_total
  FROM orders
  WHERE order_status IS DISTINCT FROM 'cancelled'
),
with_country AS (
  SELECT o.order_total, s.country_name
  FROM order_level o
  JOIN stores s ON o.store_id = s.store_id
)
SELECT country_name, SUM(order_total) AS revenue
FROM with_country
GROUP BY country_name
ORDER BY revenue DESC;

Esto mantiene el grano de pedido durante todo el proceso, así que order_total se suma una vez por pedido y no hay multiplicación de filas. stores tiene una fila limpia por tienda, por lo que la unión añade un país sin duplicar nada. Ejecuta la versión con CTE y Reino Unido queda primero con 95,706.22. Después lee en voz alta cada nombre de CTE: «nivel de pedido, luego con país, luego ingresos por país». Leer una consulta en voz alta es una técnica de revisión barata y realmente eficaz, porque se nota enseguida cuando un nombre no coincide con lo que hace el paso.

El orden en que SQL se ejecuta realmente

Escribes SELECT primero, pero la base de datos lo ejecuta tarde. El orden lógico es:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

Esto explica dos cosas que confunden a los principiantes. Un alias de columna definido en SELECT no puede usarse en WHERE, porque WHERE se ejecuta primero. Y HAVING puede filtrar una agregación porque se ejecuta después de GROUP BY, mientras que WHERE no puede.

Tu tarea

Refactoriza la consulta de un solo bloque en CTE en queries/04-cte.sql, un CTE por paso lógico, cada uno con el nombre de lo que produce - order_level, luego with_country y después la agregación. Mantén el grano de pedido para evitar la multiplicación de filas. Ejecuta ambas versiones y confirma que devuelven los mismos números, con Reino Unido primero con 95,706.22. Una consulta de varias líneas funciona dentro de las comillas - pégala tal cual:

bruin query --connection duckdb-default \
  --description "revenue by country, CTE version" \
  --query "WITH order_level AS (
  SELECT order_id, store_id, order_total
  FROM orders
  WHERE order_status IS DISTINCT FROM 'cancelled'
),
with_country AS (
  SELECT o.order_total, s.country_name
  FROM order_level o
  JOIN stores s ON o.store_id = s.store_id
)
SELECT country_name, SUM(order_total) AS revenue
FROM with_country
GROUP BY country_name
ORDER BY revenue DESC;"

Comprueba tu comprensión

• ¿Qué representa una fila de cada CTE?
• ¿Por qué HAVING puede filtrar SUM(order_total) cuando WHERE no puede?
• ¿Qué se ejecuta primero, SELECT o FROM?

Hazlo con tu agente

Di next lesson y tu agente te enseñará esto, te hará estas preguntas y después asignará la tarea anterior. Hazla a mano y luego di review my work - ejecutará tu consulta, la comparará con una rúbrica y te dirá qué corregir o marcará la lección como terminada.