The shop owner asks: who are our best customers? Answering takes everything from this course in one query.
Think about what the answer needs:
- The customer's name is in
customers. - What they bought is in
orders, which only has aquantity. - The price of each product is in
products.
So all three tables must be joined. The money spent on one order line is orders.quantity * products.price. Adding that up per customer needs SUM and GROUP BY.
Build it in steps
Experienced people do not write a query like this in one go. They grow it, running it after every step:
- Join the three tables and select the customer name, the quantity and the price. You should get 14 rows, one per order.
- Replace quantity and price with the calculation
orders.quantity * products.price. - Wrap the calculation in
SUM( ), give it an alias and addGROUP BY customers.name. Now there is one row per customer. - Add a
COUNT(*)to see how many orders each customer placed. - Sort by the total, highest first, and keep the top three.
The order of the parts
With everything in play, the full order of a query is:
SELECT … FROM … JOIN … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT
When the checks pass you have finished the course. Your certificate is on the course page.
Tables in the practice database
| Table | Columns |
|---|---|
customers | id, name, city, joined |
products | id, name, category, price, stock |
orders | id, customer_id, product_id, quantity, ordered |
Every run starts from a fresh copy of the database, so nothing you do can break it.
Your Task
- Write a query that returns the three customers who spent the most.
- Return three columns: the customer's
name, the number of orders asorders, and the money spent astotal(quantity times price, added up, rounded to 2 decimals). - Sort by
total, highest first.
- The query joins the three tables and uses
GROUP BY - The result has the columns
name,ordersandtotal - Exactly three customers are returned
- They are the three biggest spenders, with the right order counts and totals, highest first
Lesson complete!
Get Your Certificate