E-commerce: Count Orders Per Customer

SQL coding challenge · Difficulty: easy · +50 XP

Problem

Customer support needs to see how active each customer is. Show the total number of orders per customer. Include customers who have never ordered (show 0).

Tables

Table: Customer

| customer_id | name | email |
| --- | --- | --- |
| 1 | Prince | prince@gmail.com |
| 2 | Mohit | mohit@gmail.com |
| 3 | Rahul | rahul@gmail.com |
| 4 | Krishna | krishna@gmail.com |

Table: Orders

| order_id | customer_id | order_date | total_amount |
| --- | --- | --- | --- |
| 101 | 1 | 2024-05-01 | 1500 |
| 102 | 2 | 2024-05-02 | 2300 |
| 103 | 3 | 2024-05-03 | 1800 |
| 104 | 4 | 2024-05-04 | 2700 |
| 105 | 1 | 2024-05-05 | 1100 |

Expected Output

| name | order_count |
| --- | --- |
| Prince | 2 |
| Mohit | 1 |
| Rahul | 1 |
| Krishna | 1 |

Return: customer_id, name, total_orders
Include customers with 0 orders
Sort by total_orders descending