lección 9

Subqueries y CTEs: consultas modulares

Escribir consultas complejas de forma legible con Common Table Expressions y subconsultas.

⏱ 55 min

Has llegado al punto donde las queries se vuelven largas: filtrar por un resultado calculado, comparar cada fila con un promedio, encontrar los clientes cuyo gasto supera el doble de la media... Necesitas componer consultas — usar el resultado de una como input de otra. Hay dos formas: subqueries (consultas anidadas) y CTEs (WITH ... AS). Ambas resuelven el mismo problema, pero las CTEs son más legibles y mantenibles.

La analogía: imagina que estás resolviendo un problema de matemáticas complejo. Puedes escribirlo todo en una sola línea (subquery anidada) o puedes ir paso a paso: "primero calculo X, luego uso X para calcular Y, finalmente uso Y para el resultado". Las CTEs son el "paso a paso": defines cálculos intermedios con nombres claros y luego los combinas.

### Subqueries en WHERE: filtrar por un cálculo

Una subquery es una consulta dentro de otra. El uso más simple es en WHERE: "dame los pedidos cuyo importe es mayor que la media". No puedes poner AVG(importe) directamente en WHERE (recuerda: WHERE no admite funciones de agregación). Pero puedes calcular la media en una subquery:

1-- Pedidos con importe mayor que la media
2SELECT id, cliente_id, importe, fecha
3FROM pedidos
4WHERE importe > (SELECT AVG(importe) FROM pedidos)
5ORDER BY importe DESC
6LIMIT 10;
7
8-- Clientes que han gastado más que el cliente promedio
9SELECT cliente_id, SUM(importe)::INTEGER AS gasto
10FROM pedidos
11WHERE estado = 'completado'
12GROUP BY cliente_id
13HAVING SUM(importe) > (
14    SELECT AVG(total) FROM (
15        SELECT SUM(importe) AS total
16        FROM pedidos
17        WHERE estado = 'completado'
18        GROUP BY cliente_id
19    )
20)
21ORDER BY gasto DESC;

Subquery escalar en WHERE: devuelve UN valor que usas para comparar

### Subqueries con IN: filtrar por una lista

Si la subquery devuelve múltiples filas (una lista de IDs, por ejemplo), usa IN en vez de = para comparar:

1-- Clientes que han hecho pedidos de más de 400€
2SELECT nombre, email, ciudad
3FROM clientes
4WHERE id IN (
5    SELECT DISTINCT cliente_id
6    FROM pedidos
7    WHERE importe > 400
8);
9
10-- Productos que se han vendido en Madrid
11SELECT nombre, categoria, precio
12FROM productos
13WHERE id IN (
14    SELECT DISTINCT p.producto_id
15    FROM pedidos p
16    JOIN clientes c ON c.id = p.cliente_id
17    WHERE c.ciudad = 'Madrid'
18);

Subquery con IN: "dame los X que están en esta lista calculada"

Cuidado con NOT IN: si la subconsulta devuelve algún NULL, el resultado es SIEMPRE vacío — sin error, sin aviso, simplemente no sale nada. Es el error más difícil de encontrar en SQL. Si usas NOT IN, añade WHERE columna IS NOT NULL dentro de la subconsulta. O mejor: usa NOT EXISTS, que no tiene ese problema.

### CTEs: la revolución de WITH ... AS

Las CTEs (Common Table Expressions) son la forma moderna y legible de escribir consultas complejas. Defines "tablas temporales" con nombre usando WITH ... AS y luego las usas como si fueran tablas reales. Se leen de arriba a abajo, como un programa:

1-- CTE: primero calculo gasto por cliente, luego filtro
2WITH gasto_por_cliente AS (
3    SELECT 
4        cliente_id, 
5        COUNT(*) AS pedidos,
6        SUM(importe) AS total
7    FROM pedidos
8    WHERE estado = 'completado'
9    GROUP BY cliente_id
10)
11SELECT 
12    c.nombre, 
13    c.ciudad, 
14    g.pedidos,
15    g.total::INTEGER AS gasto_total
16FROM gasto_por_cliente g
17JOIN clientes c ON c.id = g.cliente_id
18WHERE g.total > 1000
19ORDER BY g.total DESC
20LIMIT 10;

CTE: defines el cálculo arriba, lo usas abajo. Legible y debuggeable.

Consejo de senior: las CTEs son como funciones en programación — nombras un cálculo y lo reutilizas. Mi regla personal: si una subquery tiene más de 5 líneas, la extraigo a una CTE. El código se lee como una historia: "primero calculo X, con X hago Y, devuelvo Z".

### Múltiples CTEs: consultas paso a paso

Puedes definir varias CTEs separadas por comas. Cada una puede referenciar las anteriores. Esto te permite descomponer consultas muy complejas en pasos simples:

1-- Múltiples CTEs: análisis de clientes por segmento
2WITH gasto AS (
3    SELECT cliente_id, SUM(importe) AS total
4    FROM pedidos
5    WHERE estado = 'completado'
6    GROUP BY cliente_id
7),
8segmentos AS (
9    SELECT 
10        cliente_id,
11        total,
12        CASE 
13            WHEN total > 400 THEN 'premium'
14            WHEN total > 100 THEN 'regular'
15            ELSE 'bajo'
16        END AS segmento
17    FROM gasto
18)
19SELECT 
20    s.segmento,
21    COUNT(*) AS clientes,
22    ROUND(AVG(s.total), 2) AS gasto_medio,
23    SUM(s.total)::INTEGER AS gasto_total_segmento
24FROM segmentos s
25GROUP BY s.segmento
26ORDER BY gasto_total_segmento DESC;

Dos CTEs encadenadas: primero calcula gasto, luego segmenta, finalmente resume

### CTE vs Subquery: cuándo usar cada una

Subquery simple (1-3 líneas, valor escalar) → déjala inline. Es más concisa.
Subquery compleja (5+ líneas, se reutiliza) → extráela a CTE. Es más legible.
Múltiples pasos de transformación → siempre CTEs. Cada paso tiene nombre y es debuggeable.
DuckDB optimiza ambas igual de bien — la decisión es de LEGIBILIDAD, no de rendimiento.
En code review, una query con 3 CTEs nombradas se aprueba en minutos. La misma lógica con 3 subqueries anidadas tarda 30 minutos en entenderse.

Subqueries anidadas a 3+ niveles de profundidad son un code smell. Si escribes SELECT ... WHERE x IN (SELECT ... FROM (SELECT ...)), refactoriza con CTEs. Tu yo futuro (y tus compañeros de equipo) te lo agradecerán.

### Patrón avanzado: comparar cada fila con su grupo

Un patrón muy frecuente en análisis: "mostrar cada pedido junto con la media de su grupo". Esto requiere calcular la media por grupo en una CTE y luego hacer JOIN con los datos individuales:

1-- Cada pedido vs la media de su ciudad
2WITH media_ciudad AS (
3    SELECT 
4        c.ciudad, 
5        ROUND(AVG(p.importe), 2) AS media
6    FROM pedidos p
7    JOIN clientes c ON c.id = p.cliente_id
8    WHERE p.estado = 'completado'
9    GROUP BY c.ciudad
10)
11SELECT 
12    c.nombre,
13    c.ciudad,
14    p.importe,
15    mc.media AS media_ciudad,
16    ROUND(p.importe - mc.media, 2) AS diferencia
17FROM pedidos p
18JOIN clientes c ON c.id = p.cliente_id
19JOIN media_ciudad mc ON mc.ciudad = c.ciudad
20WHERE p.importe > mc.media * 2  -- pedidos que doblan la media
21ORDER BY diferencia DESC
22LIMIT 10;

CTE + JOIN: comparar cada fila con la estadística de su grupo

## ejercicios

[01]

Top clientes con CTE

Usa una CTE llamada "gasto" para calcular el gasto total por cliente (solo completados). Luego haz JOIN con clientes para mostrar los 10 con mayor gasto: nombre, ciudad y gasto_total (como entero).

Cargando editor...

[02]

Segmentar clientes por gasto

Usa dos CTEs: (1) "gasto" calcula el total por cliente, (2) "segmentos" clasifica cada cliente como premium (>400), regular (100-400) o bajo (<100). La query final muestra cuántos clientes hay en cada segmento y su gasto medio.

Cargando editor...

[03]

Pedidos por encima de la media

Encuentra los 15 pedidos completados con importe mayor que el promedio global (solo de completados). Muestra id, cliente_id, importe, fecha y la diferencia con la media. Usa una subquery en WHERE.

Cargando editor...

[04]

Clientes con compras grandes (IN)

Marketing quiere contactar clientes que hayan hecho al menos un pedido de más de 200€. Usa una subquery con IN para encontrar esos clientes. Muestra nombre, email y ciudad.

Cargando editor...

[05]

Pedidos que doblan la media de su ciudad

Usa una CTE para calcular la media de importe por ciudad (solo completados). Luego encuentra pedidos cuyo importe supera el doble de la media de su ciudad. Muestra: nombre del cliente, ciudad, importe, media_ciudad y diferencia.

Cargando editor...

Regístrate para guardar tu progreso.

## comentarios

Reporta erratas, ayuda a otros o comparte tu opinión. Sé constructivo.

Inicia sesión para comentar y responder.

cargando comentarios...