Count Orders Per Customer Including Zero

SQL coding challenge · Difficulty: easy · +50 XP

Problem

Customer success wants an order activity report for every customer — including those who haven't ordered yet. Zero should appear, not be excluded.

Tables

Table: Customer

| customer_id | customer_name | city |
| --- | --- | --- |
| 1 | Krishna | Hyderabad |
| 2 | Sudheer | Bangalore |
| 3 | Atul | Pune |
| 4 | Rahul | Hyderabad |

Table: Orders

| order_id | customer_id | order_date | amount | aspect |
| --- | --- | --- | --- | --- |
| 101 | 1 | 2024-05-01 | 2500.00 | Electronics |
| 102 | 1 | 2024-05-03 | 1500.00 | Books |
| 103 | 2 | 2024-05-05 | 3000.00 | Clothing |
| 104 | 4 | 2024-05-07 | 1200.00 | Books |
| 105 | 4 | 2024-05-10 | 2200.00 | Electronics |

Expected Output

| customer_id | customer_name | city | order_count |
| --- | --- | --- | --- |
| 1 | Krishna | Hyderabad | 2 |
| 2 | Sudheer | Bangalore | 1 |
| 3 | Atul | Pune | 0 |
| 4 | Rahul | Hyderabad | 2 |

• Return: customer_id, customer_name, city, order_count
• Customers with no orders must show 0 (not NULL, not excluded)
• Sort by customer_id ascending