Schema and sample data
| customer_id | name |
|---|---|
| 1 | Ava Torres |
| 2 | Ben Ncube |
| 3 | Cara Singh |
| 4 | Deng Liu |
| 5 | Emma Rossi |
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 103 | 3 |
| 104 | 3 |
| 105 | 3 |
| 106 | 5 |
| 107 | 2 |
Show setup SQL (copy to run)
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL REFERENCES Customers(customer_id)
);
INSERT INTO Customers (customer_id, name) VALUES
(1, 'Ava Torres'),
(2, 'Ben Ncube'),
(3, 'Cara Singh'),
(4, 'Deng Liu'),
(5, 'Emma Rossi');
INSERT INTO Orders (order_id, customer_id) VALUES
(101, 1),
(102, 1),
(103, 3),
(104, 3),
(105, 3),
(106, 5),
(107, 2);You have a Customers table and an Orders table. Return the name of every customer who placed more than one order, along with their order count aliased as order_count. Sort the result by order_count from highest to lowest.
Expected result
| name | order_count |
|---|---|
| Cara Singh | 3 |
| Ava Torres | 2 |
Show hint
Join Orders to Customers, then GROUP BY the customer so each group is one person. Filter the groups with HAVING COUNT(*) > 1 (a plain WHERE cannot see an aggregate), and sort by the count.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT c.name, COUNT(*) AS order_count
FROM Customers c
JOIN Orders o ON o.customer_id = c.customer_id
GROUP BY c.name
HAVING COUNT(*) > 1
ORDER BY order_count DESC;The JOIN matches each order to its customer, producing one row per order with the customer name attached. Deng Liu has no matching orders, so an inner join drops that customer entirely, which is exactly what we want since a customer with zero orders can never have more than one.
GROUP BY c.name collapses those rows into one group per customer, and COUNT(*) counts the orders in each group: Ava has 2, Cara has 3, Ben and Emma have 1 each. The HAVING COUNT(*) > 1 clause then keeps only the groups whose count exceeds one, removing Ben and Emma. Use HAVING here rather than WHERE, because WHERE is applied before rows are grouped and cannot reference the aggregate. See the COUNT function for more.
Finally ORDER BY order_count DESC sorts the surviving customers from most orders to fewest, so Cara Singh (3) comes before Ava Torres (2). A common mistake is grouping by name when two different people share a name; in production you would GROUP BY c.customer_id (and select the name) to keep distinct customers separate.