Bonus: Total Payment Per Customer (3-Table JOIN)

SQL coding challenge · Difficulty: medium · +100 XP

Problem

Finance needs total payment per customer — tracing through three tables: Customer → Orders → Payments. Customers with no payments → show 0.

Tables

Table: Customers

| customer_id | name | city |
| --- | --- | --- |
| 1 | Abha | Delhi |
| 2 | Parvati | Mumbai |
| 3 | Pinki | Bangalore |
| 4 | Krishna | Chennai |

Table: Orders

| order_id | customer_id | order_date |
| --- | --- | --- |
| 101 | 1 | 2024-05-01 |
| 102 | 1 | 2024-05-05 |
| 103 | 2 | 2024-05-03 |
| 104 | 3 | 2024-05-07 |
| 105 | 4 | 2024-05-10 |

Table: Payments

| payment_id | order_id | amount |
| --- | --- | --- |
| 201 | 101 | 2800.00 |
| 202 | 102 | 1500.00 |
| 203 | 103 | 3200.00 |
| 204 | 104 | 2100.00 |
| 205 | 106 | 1900.00 |

Expected Output

| customer_id | name | total_payment |
| --- | --- | --- |
| 1 | Abha | 4300.00 |
| 2 | Parvati | 3200.00 |
| 3 | Pinki | 2100.00 |
| 4 | Krishna | 0.00 |

• Return: customer_id, name, total_payment
• Customers with no payments → 0.00
• Sort by total_payment descending