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 | 1 |
| 104 | 3 |
| 105 | 3 |
| 106 | 5 |
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
);
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, 1),
(104, 3),
(105, 3),
(106, 5);You have a Customers table and an Orders table. Return each customer name together with the number of orders they have placed, aliased as order_count. Include only customers who have at least one order, and sort the result by order_count from highest to lowest.
Expected result
| name | order_count |
|---|---|
| Ava Torres | 3 |
| Cara Singh | 2 |
| Emma Rossi | 1 |
Show hint
Join Customers to Orders on customer_id, then GROUP BY the customer so each group holds one customer and all of their order rows. COUNT(*) counts the rows in each group. An INNER JOIN naturally drops customers who never ordered, because they produce no matching rows.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT c.name, COUNT(*) AS order_count
FROM Customers c
INNER JOIN Orders o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY order_count DESC;The INNER JOIN matches each customer to their order rows on customer_id. Ava Torres has three matching rows, Cara Singh has two, and Emma Rossi has one, so after the join those customers appear that many times. Ben Ncube and Deng Liu have no orders, so the inner join produces no rows for them and they drop out of the result entirely.
GROUP BY c.name collapses the joined rows into one row per customer, and COUNT(*) counts the rows in each group, which is the number of orders. See the COUNT function reference for the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column). Finally ORDER BY order_count DESC sorts the busiest customers to the top.
One important variant: because this uses an INNER JOIN, customers with zero orders are silently excluded. If you need every customer listed, including those with a count of 0, switch to a LEFT JOIN and count the joined key rather than the rows: COUNT(o.order_id) instead of COUNT(*). COUNT(*) would wrongly return 1 for a customer with no orders because the outer join still yields one all-NULL row, whereas COUNT(order_id) ignores that NULL and returns 0. This same count-per-group pattern extends to the medium level.