Total Amount Spent Per Customer (With Zero)

SQL coding challenge · Difficulty: easy · +50 XP

Problem

Finance needs a customer spending report. Show the total money each customer has spent. Customers who never ordered should show 0.00, 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 | total_spent |
| --- | --- | --- |
| 1 | Krishna | 4000.00 |
| 2 | Sudheer | 3000.00 |
| 3 | Atul | 0.00 |
| 4 | Rahul | 3400.00 |

• Return: customer_id, customer_name, total_amount
• Customers with no orders → 0.00 (use COALESCE)
• Sort by total_amount descending