The bakery, stage 3: the questions
In short: Stage 3 answers the bakery's questions with JOIN, GROUP BY, LEFT JOIN, WITH and COALESCE: the menu, the bills, the best sellers and the revenue by city.
Problem
What they want to know
The bakery has data. Now it wants answers: what to write in the menu, how much each customer paid, what to bake more of, whom to send a discount, which orders are big and where the money comes from.
Every question is one query to the database from stage 2. Nothing is written: the database is the same after every query.
The idea
The cheap things
The simplest question is to one table: the products under €2, from the cheapest up.
Try it
The menu
Make the menu: the category (column category), the name and the price of every product. Order it by category in the order of their numbers (bread, pastries, drinks), and within the category from the cheapest product.
Solution
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 and ORDER BY c.id, p.price. With ORDER BY c.name the categories come out in alphabetical order, not in the order of the menu. With p.price DESC the dearest comes first in the category.
The idea
The bill
Every row of the order costs quantity * unit_price. Desislava's order 5:
The sum of the order is the total of these rows: SUM(quantity * unit_price) with GROUP BY the order.
Try it
Everyone's bills
For every order that is not cancelled: the number, the name of the customer, the date and the sum (column total, rounded to 2 places). Order by number.
Solution
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;Three tables: orders, customers and order_items, WHERE o.status <> 'cancelled', GROUP BY o.id and ROUND(SUM(i.quantity * i.unit_price), 2). Without the condition the cancelled order 4 for €15.60 comes out too. With SUM(i.unit_price) the three croissants count as one.
More precisely, if you are curious
GROUP BY o.id, while SELECT has c.name and o.ordered_at, which are not in the group. SQLite allows it. PostgreSQL accepts o.ordered_at, because the primary key of orders is in the group, but not c.name, which is from another table. There you write GROUP BY o.id, c.name, and then the query works everywhere.
Try it
What to bake more of
The three best-selling products by pieces, without the cancelled orders: the name and the count (column pieces), from the best-selling. With equal pieces by name.
Solution
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 and LIMIT 3. With COUNT(*) the rows are counted, not the pieces: Desislava's six croissants become one. With the cancelled order the baklava gets to the top with 6 pieces that nobody took.
The idea
Who has not ordered
LEFT JOIN keeps every customer, even without an order. There the columns of the order are NULL, and WHERE o.id IS NULL leaves exactly those:
Try it
A discount for the new ones
The bakery sends a discount to everyone who has no real order: no orders or only cancelled ones. Show their names in alphabetical order.
Solution
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;The condition on the status is in ON: LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'. Then Boris's cancelled order is not joined and he stays with NULL. If the condition is in WHERE, the rows with NULL drop out (NULL <> 'cancelled' is not true) and nobody is left.
Try it
The big orders
Which orders (without the cancelled ones) are above the average sum per order? The number and the sum (total, rounded to 2 places), by number. Make the sums with WITH totals AS (…).
Solution
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), then WHERE total > (SELECT AVG(total) FROM totals). The average is €9.40. With the cancelled order it becomes €10.43 and other orders come out. AVG(quantity * unit_price) from order_items is the average per row (€5.22), not per order.
Exercise
Where the money comes from
The revenue by city from the orders that are not cancelled, for all the cities of the customers. A city without revenue has 0, and the customers without a city are under 'no city'. Columns city and revenue (rounded to 2 places), from the largest revenue.
Solution
SELECT COALESCE(c.city, 'no city') 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;Two LEFT JOINs from customers, the status in ON, GROUP BY c.city, COALESCE(c.city, 'no city') and COALESCE(SUM(…), 0). With an ordinary JOIN the city without orders disappears. Without the condition in ON Sofia also gets Boris's cancelled order.
More precisely, if you are curious
SUM over a group with only NULL gives NULL, not 0. That is why it is COALESCE(SUM(…), 0). The sum of an order is a decimal, so it is ROUND: 0.1 + 0.2 in REAL is not exactly 0.3.
The idea
What comes next
All these queries only read. The next stage changes the data: prices, statuses, stock, deleted orders.
Test