Problem
Task
Return each customer_id and the number of orders placed by that customer.
Schema
Table Schema
orders(id, customer_id, total_amount, ordered_at)Input
Sample Data
| id | customer_id | total_amount | ordered_at |
|---|---|---|---|
| 101 | 1 | 58.5 | 2026-03-02 |
| 102 | 1 | 44 | 2026-03-05 |
| 103 | 2 | 104 | 2026-03-07 |
Output
Expected Output
| customer_id | order_count |
|---|---|
| 1 | 2 |
| 2 | 1 |
Answer
Check Your Solution
Show Answer and Explanation
Correct Answer
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;Explanation
GROUP BY creates one group for each customer_id. COUNT(*) then counts how many rows are in each group.
Common Mistakes
- Selecting id together with customer_id without grouping by id.
- Using COUNT(customer_id) when the intent is to count all order rows.
- Forgetting to give the aggregate result a readable alias.
Concepts
Related Concepts
GROUP BY
COUNT
Aggregate Functions
Aggregation
Next practice