Cargando
Lección 16 de 18· Práctica integradora: un informe de ventas
Llegó el momento de juntarlo todo. En las lecciones anteriores aprendiste a combinar tablas con INNER y LEFT JOIN y a resumir datos con GROUP BY, HAVING y funciones de agregación. Ahora vas a usar ambas cosas a la vez, como en el trabajo real: un analista casi nunca escribe una consulta que solo une o solo agrega; escribe consultas que unen varias tablas y resumen el resultado para responder una pregunta de negocio.
Imagina que el equipo de la tienda te pide un informe de ventas. Te llegan preguntas sueltas por chat:
«¿Quiénes son nuestros mejores clientes?» · «¿Qué categorías facturan más?» · «¿A qué clientes deberíamos reactivar?» · «¿Cuánto vale un pedido promedio en cada país?» · «¿Cuál es el producto estrella de cada categoría?»
Cada una de esas preguntas es un reto calificado en esta lección. Vas a resolverlas una por una sobre el esquema de la tienda que ya conoces, ejecutando SQL de verdad en tu navegador. Cada reto trae enunciado, tres pistas graduales y una solución de referencia. Al final tendrás un informe completo y un gráfico del resultado.
No aparecen conceptos nuevos: es práctica deliberada de lo que ya sabes. Si un reto se resiste, abre las pistas en orden; están pensadas para desbloquearte sin regalarte la respuesta.
Antes de arrancar, recordemos cómo se conectan las cinco tablas. El importe de cada línea vendida vive en order_items (quantity * unit_price), pero para saber quién compró o de qué categoría es un producto tienes que recorrer las relaciones:
La ruta mental para casi todos los retos es la misma:
customers → orders → order_items → products → categories
Según la pregunta usarás un tramo u otro de esa cadena. Para calentar, ejecuta esta consulta: une orders, customers y order_items y calcula el total de cada pedido. Fíjate en cómo un solo GROUP BY o.order_id colapsa las varias líneas de un pedido en una sola cifra.
-- Calentamiento: total de cada pedido, con el nombre del cliente
SELECT o.order_id,
c.name,
o.status,
SUM(oi.quantity * oi.unit_price) AS total_pedido
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.name, o.status
ORDER BY o.order_id;Ocho pedidos, ocho totales. Ten en cuenta que el pedido 1009 no aparece: como fue un INNER JOIN con order_items y ese pedido no tiene líneas (y además su customer_id es nulo), queda fuera del resultado. Ese detalle será importante en el Reto 5. Por ahora, ese resultado ya es un mini-informe. Subamos el nivel, reto a reto.
El equipo comercial quiere premiar a los clientes que más han gastado. Necesitas el gasto total de cada cliente sumando todas sus líneas de todos sus pedidos, y quedarte con el top 3.
El importe está en order_items, pero el nombre del cliente está en customers: orders es el puente entre ambos. Es un JOIN de tres tablas seguido de un GROUP BY por cliente.
Devuelve las columnas name y gasto_total de los 3 clientes que más han gastado en total (sumando quantity * unit_price de todas las líneas de todos sus pedidos), ordenados de mayor a menor gasto.
-- Reto 1: top 3 clientes por gasto total (todos los pedidos)
-- Une customers -> orders -> order_items, suma por cliente y quédate con 3.
SELECT c.name
-- TODO: añade SUM(...) AS gasto_total
FROM customers c
-- TODO: JOIN orders ...
-- TODO: JOIN order_items ...
-- TODO: GROUP BY / ORDER BY / LIMIT
;Elena encabeza con 725 000. Observa el patrón que se repetirá toda la lección: unir para traer las columnas, agrupar para resumir, ordenar para rankear.
Ahora una pregunta de catálogo: ¿qué categorías generan más ingresos? El importe sigue en order_items, pero la categoría está dos saltos más allá: order_items → products → categories. Suma los ingresos por categoría y ordénalos de mayor a menor.
Nota que aquí un INNER JOIN es lo correcto: la categoría Jardín no tiene ventas, así que no debe aparecer en un informe de ingresos. Un INNER JOIN la descarta de forma natural.
Devuelve las columnas name (nombre de la categoría) e ingresos (suma de quantity * unit_price de todos los productos de esa categoría), ordenadas de mayor a menor ingreso. Deben aparecer las 4 categorías con ventas.
-- Reto 2: ingresos por categoría (todos los pedidos)
-- Recorre categories -> products -> order_items.
SELECT cat.name
-- TODO: SUM(...) AS ingresos
FROM categories cat
-- TODO: JOIN products ...
-- TODO: JOIN order_items ...
-- TODO: GROUP BY / ORDER BY
;Electrónica lidera con 660 000. Con dos consultas ya tienes media página de informe.
Marketing quiere una campaña de reactivación: los clientes que nunca han recibido un pedido entregado. Ojo, aquí hay dos casos distintos que deben caer en la misma lista:
'entregado' (por ejemplo, solo enviado o cancelado).Este es el patrón LEFT JOIN + IS NULL que viste antes, con una trampa clásica: la condición status = 'entregado' debe ir en el ON, no en el WHERE. Si la pones en WHERE, conviertes el LEFT JOIN en un INNER JOIN y pierdes justo a los clientes sin pedidos que quieres encontrar.
Devuelve la columna name de todos los clientes que NO tienen ningún pedido con status 'entregado' (incluye a quienes no tienen pedidos), ordenados alfabéticamente por name.
-- Reto 3: clientes sin ningún pedido entregado
-- Usa LEFT JOIN + IS NULL. El filtro de status va en el ON, no en el WHERE.
SELECT c.name
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
-- TODO: añade aquí la condición de status = 'entregado'
-- TODO: WHERE ... IS NULL
ORDER BY c.name;Bruno (solo enviado), Diego (solo enviado) y Felipe (sin pedidos) caen en la lista. Prueba a mover AND o.status = 'entregado' al WHERE y ejecuta de nuevo: verás cómo Felipe desaparece del resultado. Ese es exactamente el error que la condición en el ON evita.
Finanzas pregunta cuánto vale un pedido promedio en cada país, contando solo pedidos entregados. Aquí conviene definir bien la métrica:
No es AVG(unit_price) (eso promedia líneas sueltas, no pedidos). El truco está en el denominador: como cada pedido tiene varias líneas en order_items, contar filas te daría un número inflado. Usa COUNT(DISTINCT o.order_id) para contar pedidos, no líneas. Envuelve la división en ROUND(..., 2) para un resultado limpio con dos decimales.
Considerando solo pedidos con status 'entregado', devuelve country, pedidos (número de pedidos distintos) y ticket_promedio (ingresos totales dividido por número de pedidos, redondeado a 2 decimales) por país, ordenado por ticket_promedio de mayor a menor.
-- Reto 4: ticket promedio por país (solo pedidos entregados)
-- ticket_promedio = SUM(importe) / COUNT(DISTINCT pedidos)
SELECT c.country,
COUNT(DISTINCT o.order_id) AS pedidos
-- TODO: añade ROUND(SUM(...) / COUNT(DISTINCT ...), 2) AS ticket_promedio
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
-- TODO: WHERE status = 'entregado'
-- TODO: GROUP BY country / ORDER BY
;Chile tiene el ticket más alto (un único pedido grande de 470 000), mientras Colombia baja porque reparte 345 000 entre dos pedidos. Cambiar COUNT(DISTINCT o.order_id) por un simple COUNT(*) te daría cifras infladas: pruébalo y verás cómo Colombia "promedia" sobre líneas en vez de pedidos.
Última pregunta del informe: dentro de cada categoría, ¿qué producto factura más? Vas a construir un ranking de productos por ingresos dentro de cada categoría, ordenado por categoría y, dentro de ella, por ingresos de mayor a menor. Así el producto estrella de cada categoría queda siempre en la primera fila de su grupo.
Recuerda que solo aparecen los productos que se han vendido: como usamos INNER JOIN con order_items, los productos sin ventas (por ejemplo Cámara de seguridad o Esterilla de yoga) quedan fuera del ranking, que es justo lo que queremos en un informe de facturación.
Nota: quedarte con solo una fila por categoría en una única consulta (sin listar el resto) es justo lo que resuelven las funciones de ventana, una técnica que verás más adelante en esta ruta de análisis de datos. Por ahora, ordenar y leer el primero de cada grupo es una técnica perfectamente válida.
Devuelve categoria (nombre de la categoría), producto (nombre del producto) e ingresos (suma de quantity * unit_price de ese producto), de todos los productos que se han vendido. Ordena por nombre de categoría (alfabético) y, dentro de cada categoría, por ingresos de mayor a menor. El primero de cada categoría es su producto estrella.
-- Reto 5: ranking de productos por ingresos dentro de cada categoría
-- categories -> products -> order_items
SELECT cat.name AS categoria,
p.name AS producto
-- TODO: SUM(...) AS ingresos
FROM categories cat
JOIN products p ON p.category_id = cat.category_id
JOIN order_items oi ON oi.product_id = p.product_id
-- TODO: GROUP BY categoria y producto
-- TODO: ORDER BY cat.name, ingresos DESC
;El producto estrella de cada categoría queda arriba: Mancuernas en Deportes, Teclado mecánico en Electrónica, Lámpara de escritorio en Hogar y SQL para analistas en Libros.
Juntando los retos ya tienes un informe de ventas real. Los números son más contundentes en una gráfica: aquí están los ingresos por categoría del Reto 2, la vista que la dirección querrá ver primero.
En cinco retos combinaste, sin conceptos nuevos, todo el curso:
| Reto | JOINs | Agregación | Truco clave |
|---|---|---|---|
| Top clientes | 3 tablas (INNER) | SUM + LIMIT | El importe vive en order_items |
| Categorías | 3 tablas (INNER) | SUM | Cruzar products para llegar a la categoría |
| Reactivación | LEFT JOIN | — | Filtro en el ON, no en el WHERE; + IS NULL |
| Ticket por país | 3 tablas (INNER) | SUM / COUNT | COUNT(DISTINCT order_id) para no inflar |
| Producto estrella | 3 tablas (INNER) | SUM | Ordenar por grupo y leer el primero |
El reflejo que quieres construir: une para traer las columnas que necesitas, agrupa para resumir, y cuida el denominador cuando cuentes sobre tablas con muchas filas por entidad.
¡Informe terminado! Acabas de responder cinco preguntas de negocio reales combinando joins y agregaciones sobre un esquema multitabla. En la próxima lección repasaremos los errores más comunes y las buenas prácticas para que estas consultas te salgan bien a la primera.
Pero antes de cerrar, un último reto de recuperación para dejar bien fijado el patrón que más se confunde: JOIN + GROUP BY + HAVING.
Este último reto pone a prueba JOIN + GROUP BY + HAVING. La diferencia clave que no debes olvidar: WHERE filtra filas antes de agrupar; HAVING filtra grupos después de agrupar. Para filtrar por el resultado de un COUNT, necesitas HAVING — si intentas WHERE COUNT(...) >= 2, el motor se quejará, porque en el momento del WHERE todavía no existe ningún conteo. Resuélvelo y habrás cerrado el informe por tu cuenta.
Devuelve name y num_pedidos de los clientes que tienen 2 o más pedidos (de cualquier estado). Ordena por num_pedidos de mayor a menor y, en caso de empate, por name alfabéticamente.
-- Reto final: clientes con 2 o más pedidos
-- Cuenta pedidos por cliente y filtra los grupos con HAVING.
SELECT c.name,
COUNT(o.order_id) AS num_pedidos
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
-- TODO: GROUP BY el cliente
-- TODO: HAVING para quedarte con los que tienen 2 o más pedidos
ORDER BY num_pedidos DESC, c.name;Gratis