Write a query to find customers who placed more than one order?
Interview preparation resource from Gate Smashers.
Join Customers to Orders, GROUP BY the customer (customer_id and customer_name) and use HAVING COUNT(*) > 1 to keep only customers with more than one order.
| customer_id | customer_name | city |
|---|---|---|
| 1 | Aditi Sharma | Delhi |
| 2 | Rahul Verma | Mumbai |
| 3 | Simran Kaur | Chandigarh |
| 4 | Arjun Nair | Bengaluru |
| order_id | customer_id | amount | status |
|---|---|---|---|
| 501 | 1 | 2400 | Delivered |
| 502 | 1 | 3200 | Delivered |
| 503 | 2 | 1800 | Delivered |
| 504 | 2 | 4100 | Pending |
| 505 | 3 | 950 | Cancelled |
SELECT c.customer_id, c.customer_name, COUNT(*) AS order_count
FROM Customers AS c
JOIN Orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(*) > 1;Direct answer
Join the Customers and Orders tables, group by the customer, and filter groups whose order count is greater than one using HAVING COUNT(*) > 1.
- Core query pattern: JOIN → GROUP BY customer_id, customer_name → HAVING COUNT(*) > 1
Query explained step-by-step
The query joins Customers to Orders to associate each order with its customer, groups the joined rows per customer, computes the number of orders per customer with COUNT(*), and then filters to retain only groups where that count exceeds one.
- Join: Match Customers.customer_id to Orders.customer_id.
- Group: Group by the customer to aggregate orders per customer (include customer_id and customer_name to keep rows distinct).
- Filter (HAVING): Use HAVING COUNT(*) > 1 to return only customers with more than one order.
Grouping robustness
Grouping by both customer_id and customer_name prevents accidental merging of different customers who might share the same name. Including the unique identifier (customer_id) ensures portability and correctness across SQL dialects while still making results readable by including customer_name.
- Reason: customer_id guarantees uniqueness; customer_name is included for clarity.
WHERE vs HAVING (usage note)
WHERE filters individual rows before aggregation, whereas HAVING filters the grouped result after aggregate functions (like COUNT) are computed. To filter by an aggregate value (for example, number of orders per customer), use HAVING rather than WHERE.
- Practical rule: Use WHERE for row-level conditions and HAVING for conditions on aggregates.