Día 5 – Subconsultas y CTEs: queries anidadas y Common Table Expressions

Página de inicio

Día 5 – Subconsultas y CTEs: queries anidadas y Common Table Expressions

Hoy vas a aprender a escribir consultas dentro de otras consultas. Es como tener superpoderes en SQL.

Hasta ahora has trabajado con consultas directas: seleccionar, filtrar, agrupar, unir tablas. Pero a veces necesitas resultados intermedios para responder preguntas más complejas.

Las subconsultas y los CTEs (Common Table Expressions) te permiten dividir problemas complejos en pasos más simples, haciendo tu SQL más legible y poderoso.

Escenario del día

Tu jefe te pide análisis avanzados:

¿Qué usuarios han gastado más que el promedio?
¿Cuál es el producto más caro de cada categoría?
¿Qué pedidos contienen productos de más de una categoría?
Dame un ranking de los 3 mejores clientes por país

Estas preguntas requieren cálculos intermedios. Sin subconsultas tendrías que hacer varias consultas separadas y combinarlas manualmente. Con lo que aprenderás hoy, lo resuelves en una sola consulta elegante.

1. ¿Qué son las subconsultas?

Una subconsulta (o subquery) es una consulta SELECT dentro de otra consulta.

Analogía simple:

Imagina que quieres saber «¿quiénes miden más que la media?»

Primero calculas la media: 170 cm
Luego filtras: personas > 170 cm

En SQL harías lo mismo, pero en una sola consulta:

SELECT nombre, altura
FROM personas
WHERE altura > (SELECT AVG(altura) FROM personas);

La parte entre paréntesis (SELECT AVG(altura) FROM personas) es la subconsulta.

2. Tipos de subconsultas

Hay tres tipos principales según dónde las uses:

A) Subconsultas en WHERE (filtros dinámicos)

Las más comunes. Se usan para comparar valores.

-- Productos más caros que el precio promedio
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos)
ORDER BY precio DESC;

¿Qué hace?

La subconsulta calcula: AVG(precio) → por ejemplo, 350.00
La consulta principal usa ese valor: WHERE precio > 350.00

Resultado:

nombre	precio
Laptop Dell XPS 13	1299.99
iPhone 15 Pro	1199.00
Auriculares Sony	349.99

B) Subconsultas con IN/NOT IN

Útiles para comparar con una lista de valores.

-- Usuarios que han hecho pedidos
SELECT nombre, email
FROM usuarios
WHERE id IN (
    SELECT DISTINCT usuario_id 
    FROM pedidos
)
ORDER BY nombre;

Equivalente con JOIN:

SELECT DISTINCT u.nombre, u.email
FROM usuarios u
INNER JOIN pedidos p ON u.id = p.usuario_id
ORDER BY u.nombre;

Usuarios que NUNCA han hecho pedidos:

-- Usuarios sin pedidos (usando subconsulta)
SELECT nombre, email, fecha_registro
FROM usuarios
WHERE id NOT IN (
    SELECT usuario_id 
    FROM pedidos
    WHERE usuario_id IS NOT NULL
)
ORDER BY nombre;

⚠️ Importante: Siempre usa WHERE usuario_id IS NOT NULL en la subconsulta cuando uses NOT IN, porque si hay un NULL, el NOT IN puede fallar.

C) Subconsultas en SELECT (columnas calculadas)

Puedes usar subconsultas para agregar columnas calculadas.

-- Usuarios con su número de pedidos
SELECT 
    nombre,
    email,
    (SELECT COUNT(*) 
     FROM pedidos p 
     WHERE p.usuario_id = usuarios.id) AS total_pedidos
FROM usuarios
ORDER BY total_pedidos DESC;

Nota: Esta subconsulta se ejecuta por cada fila de usuarios. Es lo que se llama una subconsulta correlacionada (referencia la tabla externa).

3. Subconsultas correlacionadas

Una subconsulta correlacionada hace referencia a columnas de la consulta externa. Se ejecuta una vez por cada fila.

Ejemplo: Producto más caro de cada categoría

-- Productos que son los más caros de su categoría
SELECT 
    nombre,
    categoria,
    precio
FROM productos p1
WHERE precio = (
    SELECT MAX(precio)
    FROM productos p2
    WHERE p2.categoria = p1.categoria
)
ORDER BY categoria, precio DESC;

¿Cómo funciona?

Para cada producto (p1)
Busca el precio máximo de su categoría (p2.categoria = p1.categoria)
Si el precio del producto coincide con ese máximo, lo incluye

Resultado:

nombre	categoria	precio
Microfono Blue Yeti	Accesorios	129.00
Disco SSD 1TB	Almacenamiento	119.99
Laptop Dell XPS 13	Electronica	1299.99
Silla Gaming	Muebles	249.99

Usuarios que gastaron más que el promedio

-- Clientes VIP (gastaron más que el promedio)
SELECT 
    u.nombre,
    u.email,
    (SELECT SUM(p.total) 
     FROM pedidos p 
     WHERE p.usuario_id = u.id) AS gasto_total
FROM usuarios u
WHERE (
    SELECT SUM(p.total) 
    FROM pedidos p 
    WHERE p.usuario_id = u.id
) > (
    SELECT AVG(total_por_usuario)
    FROM (
        SELECT SUM(total) AS total_por_usuario
        FROM pedidos
        GROUP BY usuario_id
    ) AS promedios
)
ORDER BY gasto_total DESC;

Esta consulta se vuelve compleja. Aquí es donde brillan los CTEs.

4. CTEs (Common Table Expressions)

Un CTE es como crear una tabla temporal que solo existe durante la ejecución de tu consulta. Es una forma más limpia y legible de escribir subconsultas complejas.

Sintaxis básica

WITH nombre_temporal AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT ...
FROM nombre_temporal;

Ejemplo: Precio promedio

-- Sin CTE (difícil de leer)
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos);

-- Con CTE (más claro)
WITH precio_promedio AS (
    SELECT AVG(precio) AS promedio
    FROM productos
)
SELECT 
    p.nombre,
    p.precio,
    pp.promedio
FROM productos p, precio_promedio pp
WHERE p.precio > pp.promedio
ORDER BY p.precio DESC;

Usuarios VIP (con CTE)

Recordemos la consulta compleja de antes. Con CTE es mucho más clara:

-- Usuarios que gastaron más que el promedio
WITH gastos_por_usuario AS (
    SELECT 
        u.id,
        u.nombre,
        u.email,
        COALESCE(SUM(p.total), 0) AS gasto_total
    FROM usuarios u
    LEFT JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.id, u.nombre, u.email
),
promedio_gasto AS (
    SELECT AVG(gasto_total) AS promedio
    FROM gastos_por_usuario
    WHERE gasto_total > 0
)
SELECT 
    g.nombre,
    g.email,
    ROUND(g.gasto_total, 2) AS gasto_total,
    ROUND(p.promedio, 2) AS promedio_general
FROM gastos_por_usuario g, promedio_gasto p
WHERE g.gasto_total > p.promedio
ORDER BY g.gasto_total DESC;

Ventajas del CTE:

✅ Más fácil de leer
✅ Puedes reutilizar gastos_por_usuario varias veces
✅ Pasos lógicos separados (como variables en programación)

5. Múltiples CTEs

Puedes definir varios CTEs en una sola consulta. Se separan con comas.

Análisis completo de ventas por categoría

-- Ventas por categoría con comparación vs promedio
WITH ventas_por_categoria AS (
    SELECT 
        pr.categoria,
        COUNT(DISTINCT dp.pedido_id) AS num_pedidos,
        SUM(dp.cantidad) AS unidades_vendidas,
        ROUND(SUM(dp.cantidad * dp.precio_unitario), 2) AS ingresos
    FROM productos pr
    INNER JOIN detalle_pedidos dp ON pr.id = dp.producto_id
    GROUP BY pr.categoria
),
promedio_ventas AS (
    SELECT 
        AVG(ingresos) AS ingreso_promedio,
        AVG(unidades_vendidas) AS unidades_promedio
    FROM ventas_por_categoria
)
SELECT 
    v.categoria,
    v.num_pedidos,
    v.unidades_vendidas,
    v.ingresos,
    ROUND(p.ingreso_promedio, 2) AS promedio_categoria,
    CASE 
        WHEN v.ingresos > p.ingreso_promedio THEN 'Sobre el promedio'
        ELSE 'Bajo el promedio'
    END AS performance
FROM ventas_por_categoria v, promedio_ventas p
ORDER BY v.ingresos DESC;

6. Subconsultas en FROM (tablas derivadas)

Puedes usar una subconsulta como si fuera una tabla.

-- Top 3 usuarios por gasto total
SELECT 
    nombre,
    email,
    gasto_total
FROM (
    SELECT 
        u.nombre,
        u.email,
        SUM(p.total) AS gasto_total
    FROM usuarios u
    INNER JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.id, u.nombre, u.email
    ORDER BY gasto_total DESC
    LIMIT 3
) AS top_usuarios;

Equivalente con CTE (más legible):

WITH ranking_usuarios AS (
    SELECT 
        u.nombre,
        u.email,
        SUM(p.total) AS gasto_total
    FROM usuarios u
    INNER JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.id, u.nombre, u.email
    ORDER BY gasto_total DESC
    LIMIT 3
)
SELECT * FROM ranking_usuarios;

7. Window Functions con CTEs

Los CTEs son perfectos para trabajar con funciones de ventana (window functions) como ROW_NUMBER(), RANK(), DENSE_RANK().

Ranking de usuarios por gasto

-- Top 3 usuarios por país
WITH ranking_por_pais AS (
    SELECT 
        u.nombre,
        u.email,
        u.pais,
        SUM(p.total) AS gasto_total,
        ROW_NUMBER() OVER (PARTITION BY u.pais ORDER BY SUM(p.total) DESC) AS ranking
    FROM usuarios u
    INNER JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.id, u.nombre, u.email, u.pais
)
SELECT 
    pais,
    ranking,
    nombre,
    email,
    ROUND(gasto_total, 2) AS gasto_total
FROM ranking_por_pais
WHERE ranking <= 3
ORDER BY pais, ranking;

¿Qué hace ROW_NUMBER()?

PARTITION BY u.pais → Reinicia el contador para cada país
ORDER BY SUM(p.total) DESC → Ordena por gasto (mayor a menor)
Resultado: Cada usuario tiene un número de 1, 2, 3… dentro de su país

Productos con ranking por categoría

-- Top 2 productos más caros de cada categoría
WITH ranking_productos AS (
    SELECT 
        nombre,
        categoria,
        precio,
        stock,
        RANK() OVER (PARTITION BY categoria ORDER BY precio DESC) AS ranking
    FROM productos
)
SELECT 
    categoria,
    ranking,
    nombre,
    precio,
    stock
FROM ranking_productos
WHERE ranking <= 2
ORDER BY categoria, ranking;

Diferencia entre RANK() y ROW_NUMBER():

ROW_NUMBER(): Siempre da números únicos (1, 2, 3…)
RANK(): Si hay empate, da el mismo número (1, 1, 3…)
DENSE_RANK(): Si hay empate, no salta números (1, 1, 2…)

8. Casos prácticos del día a día

Detectar pedidos con múltiples categorías

-- Pedidos que contienen productos de más de una categoría
WITH categorias_por_pedido AS (
    SELECT 
        p.id AS pedido_id,
        p.fecha,
        COUNT(DISTINCT pr.categoria) AS num_categorias
    FROM pedidos p
    INNER JOIN detalle_pedidos dp ON p.id = dp.pedido_id
    INNER JOIN productos pr ON dp.producto_id = pr.id
    GROUP BY p.id, p.fecha
)
SELECT 
    cp.pedido_id,
    cp.fecha,
    cp.num_categorias,
    u.nombre AS cliente
FROM categorias_por_pedido cp
INNER JOIN pedidos p ON cp.pedido_id = p.id
INNER JOIN usuarios u ON p.usuario_id = u.id
WHERE cp.num_categorias > 1
ORDER BY cp.num_categorias DESC, cp.fecha;

Productos con stock por debajo del promedio

-- Productos con stock crítico (menos de la mitad del promedio)
WITH stock_promedio AS (
    SELECT AVG(stock) AS promedio
    FROM productos
)
SELECT 
    p.nombre,
    p.categoria,
    p.stock,
    ROUND(sp.promedio, 0) AS stock_promedio,
    ROUND((p.stock::NUMERIC / sp.promedio) * 100, 1) AS porcentaje_vs_promedio
FROM productos p, stock_promedio sp
WHERE p.stock < (sp.promedio / 2)
ORDER BY p.stock;

Análisis de recurrencia (usuarios con múltiples pedidos)

-- Usuarios recurrentes vs nuevos
WITH frecuencia_usuarios AS (
    SELECT 
        u.id,
        u.nombre,
        u.email,
        COUNT(p.id) AS total_pedidos,
        MIN(p.fecha) AS primer_pedido,
        MAX(p.fecha) AS ultimo_pedido
    FROM usuarios u
    LEFT JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.id, u.nombre, u.email
)
SELECT 
    CASE 
        WHEN total_pedidos = 0 THEN 'Sin pedidos'
        WHEN total_pedidos = 1 THEN 'Cliente nuevo'
        WHEN total_pedidos BETWEEN 2 AND 3 THEN 'Cliente regular'
        ELSE 'Cliente VIP'
    END AS tipo_cliente,
    COUNT(*) AS cantidad
FROM frecuencia_usuarios
GROUP BY tipo_cliente
ORDER BY 
    CASE tipo_cliente
        WHEN 'Cliente VIP' THEN 1
        WHEN 'Cliente regular' THEN 2
        WHEN 'Cliente nuevo' THEN 3
        WHEN 'Sin pedidos' THEN 4
    END;

Productos más rentables por categoría

-- Top producto de cada categoría por ingresos
WITH ingresos_productos AS (
    SELECT 
        pr.id,
        pr.nombre,
        pr.categoria,
        pr.precio,
        SUM(dp.cantidad) AS unidades_vendidas,
        ROUND(SUM(dp.cantidad * dp.precio_unitario), 2) AS ingresos_totales,
        RANK() OVER (PARTITION BY pr.categoria ORDER BY SUM(dp.cantidad * dp.precio_unitario) DESC) AS ranking
    FROM productos pr
    INNER JOIN detalle_pedidos dp ON pr.id = dp.producto_id
    GROUP BY pr.id, pr.nombre, pr.categoria, pr.precio
)
SELECT 
    categoria,
    nombre,
    precio,
    unidades_vendidas,
    ingresos_totales
FROM ingresos_productos
WHERE ranking = 1
ORDER BY ingresos_totales DESC;

9. CTEs recursivos (avanzado)

PostgreSQL también soporta CTEs recursivos, útiles para jerarquías o secuencias.

Ejemplo: Generar una serie de números

-- Generar números del 1 al 10
WITH RECURSIVE numeros AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM numeros
    WHERE n < 10
)
SELECT * FROM numeros;

¿Cómo funciona?

Caso base: SELECT 1 AS n → Empieza con 1
Caso recursivo: SELECT n + 1 → Suma 1 al valor anterior
Condición de parada: WHERE n < 10 → Para cuando llega a 10

Ejemplo práctico: Calendario de fechas

-- Generar todas las fechas del mes actual
WITH RECURSIVE fechas AS (
    SELECT DATE_TRUNC('month', CURRENT_DATE) AS fecha
    UNION ALL
    SELECT fecha + INTERVAL '1 day'
    FROM fechas
    WHERE fecha < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day'
)
SELECT 
    fecha,
    TO_CHAR(fecha, 'Day') AS dia_semana,
    EXTRACT(DAY FROM fecha) AS dia
FROM fechas
ORDER BY fecha;

Uso real: Generar reportes diarios incluso para días sin datos.

10. Errores comunes (y cómo evitarlos)

Error 1: Subconsulta devuelve múltiples filas

-- ❌ ERROR: La subconsulta devuelve varias filas
SELECT nombre, precio
FROM productos
WHERE precio > (
    SELECT precio 
    FROM productos 
    WHERE categoria = 'Electronica'
);

Problema: La subconsulta devuelve múltiples precios, pero > espera un solo valor.

Solución: Usa una función de agregación:

-- ✅ CORRECTO
SELECT nombre, precio
FROM productos
WHERE precio > (
    SELECT MAX(precio)
    FROM productos 
    WHERE categoria = 'Electronica'
);

Error 2: NOT IN con NULLs

-- ⚠️ PUEDE FALLAR si hay NULLs
SELECT nombre
FROM usuarios
WHERE id NOT IN (SELECT usuario_id FROM pedidos);

Solución: Filtra NULLs:

-- ✅ CORRECTO
SELECT nombre
FROM usuarios
WHERE id NOT IN (
    SELECT usuario_id 
    FROM pedidos 
    WHERE usuario_id IS NOT NULL
);

O mejor, usa LEFT JOIN:

-- ✅ MÁS EFICIENTE
SELECT u.nombre
FROM usuarios u
LEFT JOIN pedidos p ON u.id = p.usuario_id
WHERE p.id IS NULL;

Error 3: Olvidar el alias en subconsultas FROM

-- ❌ ERROR: Falta el alias
SELECT nombre, gasto
FROM (
    SELECT u.nombre, SUM(p.total) AS gasto
    FROM usuarios u
    JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.nombre
);

Solución: Siempre pon un alias:

-- ✅ CORRECTO
SELECT nombre, gasto
FROM (
    SELECT u.nombre, SUM(p.total) AS gasto
    FROM usuarios u
    JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.nombre
) AS resumen_gastos;

Error 4: CTEs no utilizados

-- ⚠️ CTE definido pero no usado
WITH datos AS (
    SELECT * FROM productos
)
SELECT * FROM usuarios;  -- No usa 'datos'

Esto no da error, pero es ineficiente. Solo define CTEs que vas a usar.

11. ¿Cuándo usar subconsultas vs JOINs vs CTEs?

Usa subconsultas cuando:

Necesitas un valor único para comparar (promedio, máximo, etc.)
La lógica es simple y corta
Es más legible que un JOIN

Usa JOINs cuando:

Necesitas combinar datos de múltiples tablas
Quieres todas las filas relacionadas
Es más eficiente que subconsultas correlacionadas

Usa CTEs cuando:

La consulta es compleja y quieres dividirla en pasos
Necesitas reutilizar resultados intermedios
Quieres que tu código sea más legible
Trabajas con window functions o consultas recursivas

Regla general: Si tu consulta tiene más de 3 niveles de anidación, probablemente un CTE la haría más clara.

12. Ejercicios prácticos

Intenta resolver estos ejercicios antes de ver las soluciones.

Productos más baratos que el precio promedio de su categoría
Usuarios que han gastado más de 1000€ en total
Productos que nunca se han vendido (usando subconsulta con NOT IN)
Categorías cuyo precio promedio es superior al precio promedio general
Ranking de los 3 productos más vendidos (por unidades)
Usuarios con más pedidos que el promedio (usa CTE)
Productos con stock superior a la media de su categoría
Pedidos con valor superior al ticket promedio de su usuario
Top 2 usuarios por gasto en cada país (usa window functions)
Análisis completo: Crea un CTE que muestre categorías con su producto más caro, más barato y precio promedio

💡 Consejo: Empieza por escribir la subconsulta o CTE por separado, verifica que funciona, y luego incorpórala a la consulta principal.

13. Soluciones

Solución Ejercicio 1

SELECT 
    p1.nombre,
    p1.categoria,
    p1.precio,
    (SELECT ROUND(AVG(precio), 2) 
     FROM productos p2 
     WHERE p2.categoria = p1.categoria) AS promedio_categoria
FROM productos p1
WHERE p1.precio < (
    SELECT AVG(precio)
    FROM productos p2
    WHERE p2.categoria = p1.categoria
)
ORDER BY p1.categoria, p1.precio;

Solución Ejercicio 2

WITH gastos_totales AS (
    SELECT 
        u.id,
        u.nombre,
        u.email,
        SUM(p.total) AS gasto_total
    FROM usuarios u
    INNER JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.id, u.nombre, u.email
)
SELECT 
    nombre,
    email,
    ROUND(gasto_total, 2) AS gasto_total
FROM gastos_totales
WHERE gasto_total > 1000
ORDER BY gasto_total DESC;

Solución Ejercicio 3

SELECT 
    nombre,
    precio,
    categoria,
    stock
FROM productos
WHERE id NOT IN (
    SELECT DISTINCT producto_id 
    FROM detalle_pedidos
    WHERE producto_id IS NOT NULL
)
ORDER BY categoria, nombre;

Solución Ejercicio 4

WITH promedio_general AS (
    SELECT AVG(precio) AS precio_promedio
    FROM productos
),
promedio_por_categoria AS (
    SELECT 
        categoria,
        AVG(precio) AS precio_promedio_cat
    FROM productos
    GROUP BY categoria
)
SELECT 
    pc.categoria,
    ROUND(pc.precio_promedio_cat, 2) AS promedio_categoria,
    ROUND(pg.precio_promedio, 2) AS promedio_general
FROM promedio_por_categoria pc, promedio_general pg
WHERE pc.precio_promedio_cat > pg.precio_promedio
ORDER BY pc.precio_promedio_cat DESC;

Solución Ejercicio 5

WITH ventas_productos AS (
    SELECT 
        pr.nombre,
        pr.categoria,
        SUM(dp.cantidad) AS unidades_vendidas,
        RANK() OVER (ORDER BY SUM(dp.cantidad) DESC) AS ranking
    FROM productos pr
    INNER JOIN detalle_pedidos dp ON pr.id = dp.producto_id
    GROUP BY pr.id, pr.nombre, pr.categoria
)
SELECT 
    ranking,
    nombre,
    categoria,
    unidades_vendidas
FROM ventas_productos
WHERE ranking <= 3
ORDER BY ranking;

Solución Ejercicio 6

WITH pedidos_por_usuario AS (
    SELECT 
        usuario_id,
        COUNT(*) AS num_pedidos
    FROM pedidos
    GROUP BY usuario_id
),
promedio_pedidos AS (
    SELECT AVG(num_pedidos) AS promedio
    FROM pedidos_por_usuario
)
SELECT 
    u.nombre,
    u.email,
    pu.num_pedidos,
    ROUND(pp.promedio, 2) AS promedio_pedidos
FROM usuarios u
INNER JOIN pedidos_por_usuario pu ON u.id = pu.usuario_id
CROSS JOIN promedio_pedidos pp
WHERE pu.num_pedidos > pp.promedio
ORDER BY pu.num_pedidos DESC;

Solución Ejercicio 7

WITH stock_promedio_categoria AS (
    SELECT 
        categoria,
        AVG(stock) AS stock_promedio
    FROM productos
    GROUP BY categoria
)
SELECT 
    p.nombre,
    p.categoria,
    p.stock,
    ROUND(spc.stock_promedio, 1) AS promedio_categoria
FROM productos p
INNER JOIN stock_promedio_categoria spc ON p.categoria = spc.categoria
WHERE p.stock > spc.stock_promedio
ORDER BY p.categoria, p.stock DESC;

Solución Ejercicio 8

WITH ticket_promedio_usuario AS (
    SELECT 
        usuario_id,
        AVG(total) AS ticket_promedio
    FROM pedidos
    GROUP BY usuario_id
)
SELECT 
    p.id AS pedido_id,
    u.nombre,
    p.fecha,
    ROUND(p.total, 2) AS total_pedido,
    ROUND(tpu.ticket_promedio, 2) AS ticket_promedio_usuario
FROM pedidos p
INNER JOIN usuarios u ON p.usuario_id = u.id
INNER JOIN ticket_promedio_usuario tpu ON p.usuario_id = tpu.usuario_id
WHERE p.total > tpu.ticket_promedio
ORDER BY p.total DESC;

Solución Ejercicio 9

WITH ranking_por_pais AS (
    SELECT 
        u.pais,
        u.nombre,
        u.email,
        SUM(p.total) AS gasto_total,
        RANK() OVER (PARTITION BY u.pais ORDER BY SUM(p.total) DESC) AS ranking
    FROM usuarios u
    INNER JOIN pedidos p ON u.id = p.usuario_id
    GROUP BY u.pais, u.id, u.nombre, u.email
)
SELECT 
    pais,
    ranking,
    nombre,
    email,
    ROUND(gasto_total, 2) AS gasto_total
FROM ranking_por_pais
WHERE ranking <= 2
ORDER BY pais, ranking;

Solución Ejercicio 10

WITH analisis_categorias AS (
    SELECT 
        categoria,
        COUNT(*) AS total_productos,
        ROUND(MIN(precio), 2) AS precio_minimo,
        ROUND(MAX(precio), 2) AS precio_maximo,
        ROUND(AVG(precio), 2) AS precio_promedio
    FROM productos
    GROUP BY categoria
),
producto_mas_caro AS (
    SELECT DISTINCT ON (categoria)
        categoria,
        nombre AS producto_mas_caro
    FROM productos
    ORDER BY categoria, precio DESC
),
producto_mas_barato AS (
    SELECT DISTINCT ON (categoria)
        categoria,
        nombre AS producto_mas_barato
    FROM productos
    ORDER BY categoria, precio ASC
)
SELECT 
    ac.categoria,
    ac.total_productos,
    pmc.producto_mas_caro,
    ac.precio_maximo,
    pmb.producto_mas_barato,
    ac.precio_minimo,
    ac.precio_promedio
FROM analisis_categorias ac
LEFT JOIN producto_mas_caro pmc ON ac.categoria = pmc.categoria
LEFT JOIN producto_mas_barato pmb ON ac.categoria = pmb.categoria
ORDER BY ac.categoria;

Resumen del Día 5

¡Excelente trabajo! Ahora dominas técnicas avanzadas para consultas complejas.

Has aprendido:

✅ Subconsultas en WHERE → Filtros dinámicos basados en cálculos
✅ Subconsultas en SELECT → Columnas calculadas
✅ Subconsultas en FROM → Tablas derivadas
✅ Subconsultas correlacionadas → Referencias a la consulta externa
✅ CTEs (WITH) → Consultas más legibles y modulares
✅ Múltiples CTEs → Dividir problemas complejos en pasos
✅ Window Functions → Rankings y análisis avanzados
✅ CTEs recursivos → Jerarquías y secuencias

Clave del día:

Las subconsultas son poderosas pero pueden ser difíciles de leer cuando se anidan
Los CTEs hacen tu SQL más legible, especialmente en consultas complejas
Usa window functions con CTEs para rankings y análisis por grupos
Elige la herramienta adecuada: subconsulta, JOIN o CTE según el caso

Siguiente paso

En el Día 6 aprenderás a modificar datos: INSERT, UPDATE, DELETE. Hasta ahora solo has leído datos. Mañana aprenderás a crear, actualizar y eliminar registros de forma segura.

También verás:

Transacciones (COMMIT, ROLLBACK)
Buenas prácticas para no perder datos
Cómo hacer backups antes de cambios importantes

¡Nos vemos en el Día 6!

Día 5 – Subconsultas y CTEs: queries anidadas y Common Table Expressions