La panadería, etapa 3: las preguntas
En resumen: La etapa 3 responde a las preguntas de la panadería con JOIN, GROUP BY, LEFT JOIN, WITH y COALESCE: la carta, las cuentas, los más vendidos y los ingresos por ciudad.
Problema
Lo que quieren saber
La panadería tiene datos. Ahora quiere respuestas: qué poner en la carta, cuánto pagó cada cliente, de qué hornear más, a quién enviar un descuento, qué pedidos son grandes y de dónde viene el dinero.
Cada pregunta es una consulta a la base de la etapa 2. No se escribe nada: la base es la misma después de cada consulta.
La idea
Lo barato
La pregunta más sencilla va a una sola tabla: los productos de menos de 2 €, del más barato hacia arriba.
Pruébalo
La carta
Haz la carta: la categoría (columna category), el nombre y el precio de cada producto. Ordénala por categoría en el orden de sus números (pan, dulces, bebidas) y, dentro de la categoría, desde el producto más barato.
Solución
SELECT c.name AS category, p.name, p.price
FROM products p
JOIN categories c ON c.id = p.category_id
ORDER BY c.id, p.price;JOIN categories c ON c.id = p.category_id y ORDER BY c.id, p.price. Con ORDER BY c.name las categorías salen en orden alfabético, no en el orden de la carta. Con p.price DESC el más caro va primero dentro de la categoría.
La idea
La cuenta
Cada línea del pedido cuesta quantity * unit_price. El pedido 5 de Desislava:
La suma del pedido es el total de estas líneas: SUM(quantity * unit_price) con GROUP BY el pedido.
Pruébalo
Las cuentas de todos
Para cada pedido que no esté cancelado: el número, el nombre del cliente, la fecha y la suma (columna total, redondeada a 2 decimales). Ordena por número.
Solución
SELECT o.id, c.name, o.ordered_at, ROUND(SUM(i.quantity * i.unit_price), 2) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.id
ORDER BY o.id;Tres tablas: orders, customers y order_items, WHERE o.status <> 'cancelled', GROUP BY o.id y ROUND(SUM(i.quantity * i.unit_price), 2). Sin la condición sale también el pedido cancelado 4, de 15,60 €. Con SUM(i.unit_price) los tres cruasanes cuentan como uno.
Más precisamente, para curiosos
GROUP BY o.id, mientras que SELECT tiene c.name y o.ordered_at, que no están en el grupo. SQLite lo permite. PostgreSQL acepta o.ordered_at, porque la clave primaria de orders está en el grupo, pero no c.name, que es de otra tabla. Allí escribes GROUP BY o.id, c.name, y entonces la consulta funciona en todas partes.
Pruébalo
De qué hornear más
Los tres productos más vendidos por unidades, sin los pedidos cancelados: el nombre y la cantidad (columna pieces), empezando por el más vendido. Con unidades iguales, por nombre.
Solución
SELECT p.name, SUM(i.quantity) AS pieces
FROM order_items i
JOIN orders o ON o.id = i.order_id
JOIN products p ON p.id = i.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.id
ORDER BY pieces DESC, p.name
LIMIT 3;SUM(i.quantity), GROUP BY p.id, ORDER BY pieces DESC, p.name y LIMIT 3. Con COUNT(*) se cuentan las filas, no las unidades: los seis cruasanes de Desislava pasan a ser uno. Con el pedido cancelado, la baklava sube a lo más alto con 6 unidades que nadie recogió.
La idea
Quién no ha pedido
LEFT JOIN conserva a todos los clientes, incluso sin pedido. Ahí las columnas del pedido son NULL, y WHERE o.id IS NULL deja justo esos:
Pruébalo
Un descuento para los nuevos
La panadería envía un descuento a todos los que no tienen un pedido real: sin pedidos o solo con pedidos cancelados. Muestra sus nombres en orden alfabético.
Solución
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
WHERE o.id IS NULL
ORDER BY c.name;La condición sobre el estado va en ON: LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'. Entonces el pedido cancelado de Boris no se une y él se queda con NULL. Si la condición está en WHERE, las filas con NULL se caen (NULL <> 'cancelled' no es verdadero) y no queda nadie.
Pruébalo
Los pedidos grandes
¿Qué pedidos (sin los cancelados) están por encima de la suma media por pedido? El número y la suma (total, redondeada a 2 decimales), por número. Calcula las sumas con WITH totals AS (…).
Solución
WITH totals AS (
SELECT o.id, SUM(i.quantity * i.unit_price) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.id
)
SELECT id, ROUND(total, 2) AS total
FROM totals
WHERE total > (SELECT AVG(total) FROM totals)
ORDER BY id;WITH totals AS (SELECT o.id, SUM(…) AS total … WHERE o.status <> 'cancelled' GROUP BY o.id), luego WHERE total > (SELECT AVG(total) FROM totals). La media es 9,40 €. Con el pedido cancelado pasa a ser 10,43 € y salen otros pedidos. AVG(quantity * unit_price) de order_items es la media por línea (5,22 €), no por pedido.
Ejercicio
De dónde viene el dinero
Los ingresos por ciudad de los pedidos que no están cancelados, para todas las ciudades de los clientes. Una ciudad sin ingresos tiene 0, y los clientes sin ciudad van bajo 'sin ciudad'. Columnas city y revenue (redondeada a 2 decimales), de mayor a menor ingreso.
Solución
SELECT COALESCE(c.city, 'sin ciudad') AS city,
ROUND(COALESCE(SUM(i.quantity * i.unit_price), 0), 2) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY c.city
ORDER BY revenue DESC;Dos LEFT JOIN desde customers, el estado en ON, GROUP BY c.city, COALESCE(c.city, 'sin ciudad') y COALESCE(SUM(…), 0). Con un JOIN normal la ciudad sin pedidos desaparece. Sin la condición en ON, Sofía recibe también el pedido cancelado de Boris.
Más precisamente, para curiosos
SUM sobre un grupo con solo NULL da NULL, no 0. Por eso se escribe COALESCE(SUM(…), 0). La suma de un pedido es un decimal, así que va con ROUND: 0,1 + 0,2 en REAL no es exactamente 0,3.
La idea
Lo que viene
Todas estas consultas solo leen. La siguiente etapa cambia los datos: precios, estados, existencias, pedidos borrados.
Test