Lesson 10 of 10
Unique values
- 10 min
- 4 checks
- 3 hints
- Go to task
Goal
DISTINCT and COUNT(DISTINCT)
01
Explanation
SELECT DISTINCT city FROM orders returns each city once, however many times it repeats. But it works on the whole row, so DISTINCT city, customer removes only duplicate pairs, and each city shows up again for every different customer.
The same goes inside a count: COUNT(customer) counts every order that has a customer name, so a customer with three orders is counted three times, while COUNT(DISTINCT customer) counts that customer once.
The gap between the two numbers tells you something useful on its own: lots of orders from few customers means your customers keep coming back.
02
Your task
For each city, show city, orders_count for the number of orders and buyers for the number of different customers.
Checks
4 checks- Columns are city, orders_count and buyers
- One row per city
- The orders_count column counts every order
- The buyers column counts each customer once
Tables in this lesson
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
city TEXT NOT NULL,
amount INTEGER NOT NULL,
rating INTEGER
);
INSERT INTO orders (customer, city, amount, rating) VALUES
('Layan', 'Riyadh', 120, 5),
('Khalid', 'Jeddah', 300, 4),
('Layan', 'Riyadh', 80, NULL),
('Reem', 'Dammam', 45, 3),
('Sara', 'Riyadh', 210, 5),
('Khalid', 'Jeddah', 50, NULL),
('Fahad', 'Jeddah', 150, 2),
('Noura', 'Riyadh', 95, 4),
('Omar', 'Mecca', 400, NULL),
('Layan', 'Riyadh', 35, 4),
('Reem', 'Dammam', 130, NULL),
('Sara', 'Riyadh', 70, 3);