Retail: Total Amount Spent Per Customer

SQL coding challenge · Difficulty: easy · +50 XP

Problem

A retail chain's loyalty program tracks customer spending. Show the total amount spent. Include customers who have never ordered (show 0).

Tables

Table: Customers

| customer_id | name | email | city |
| --- | --- | --- | --- |
| 1 | Rahul | rahul@gmail.com | Delhi |
| 2 | Prince | prince@gmail.com | Mumbai |
| 3 | Mohit | mohit@gmail.com | Bangalore |
| 4 | Sudheer | sudheer@gmail.com | Hyderabad |

Table: Products

| product_id | product_name | category | price |
| --- | --- | --- | --- |
| 101 | T-Shirt | Apparel | 599.00 |
| 102 | Jeans | Apparel | 1299.00 |
| 103 | Sneakers | Footwear | 2499.00 |
| 104 | Backpack | Accessories | 899.00 |
| 105 | Watch | Accessories | 1499.00 |

Table: Orders

| order_id | customer_id | order_date | total_amount | payment_mode |
| --- | --- | --- | --- | --- |
| 201 | 1 | 2024-05-01 | 1898.00 | UPI |
| 202 | 2 | 2024-05-02 | 2499.00 | Credit Card |
| 203 | 3 | 2024-05-03 | 1399.00 | Debit Card |
| 204 | 1 | 2024-05-04 | 599.00 | UPI |
| 205 | 4 | 2024-05-05 | 3898.00 | Credit Card |
| 206 | 2 | 2024-05-06 | 899.00 | UPI |

Expected Output

| name | total_spent |
| --- | --- |
| Sudheer | 3898.00 |
| Prince | 3398.00 |
| Rahul | 2497.00 |
| Mohit | 1399.00 |

Return: name, total_spent
Include customers who never ordered (show 0)
Sort by total_spent descending