Project: the best customers
SQL · Unit 9: Subqueries
- Full plan
- SQL
- Project
- 3 exercises
The owner wants to know who the store's best customers are. The first idea is simple. They're the ones who have ordered more units than an average customer, and the average is taken over the six who have ordered something. All twelve orders count, the one dated the 20th too.
It's three steps: how many units each customer has, what the average of those six numbers is, and who beats it. You already know how to do the first one.
SELECT c.name,
SUM(o.quantity) AS units
FROM orders o
JOIN customers c
ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY units DESC, c.name;Returns
| name | units |
|---|---|
| Mason | 10 |
| Luke | 8 |
| Ann | 4 |
| Valerie | 4 |
| Chloë | 2 |
| Dylan | 2 |
Exercises in this lesson
You do them in the app, which checks them on the spot and explains why.
1. Complete the code
The second step. The average units per customer isn't
AVG(quantity), which gives the average order, 2.5. You have to average the six sums. Fill in the blanks.2. Write the code
The third step puts the other two together. Write a query with two columns, in this order: the name of each customer who has ordered more units than the average customer, and how many. Whoever has the most goes first, and ties go by name. The average can't be typed in by hand. The subquery from the previous step calculates it, and it goes in a
HAVING, in the spot where the 5 would go.3. Predict the output
The owner isn't convinced. Mason tops the list on the strength of 10 Cables at 5.25 each. So the measure changes, from units to money. In money, Dylan has 958.9, Luke 797.5, Ann 299.9, Chloë 119.8, Valerie 61.25, and Mason 52.5. An average order is 190.82, and an average customer has 381.64. Look in the center of the query for which of the two it compares against. Inside, the tables have a 2 in their alias, because they're another copy of the outer ones. What does it return?
It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.