Advanced Joins: Payments With Order and Customer Info

SQL coding challenge · Difficulty: medium · +100 XP

Problem

The accounting team needs to reconcile payments against orders. For each payment, show the amount, the order it's linked to, and the customer name.

Tables

Table: Customers

| customer_id | name | city |
| --- | --- | --- |
| 1 | Atul | Delhi |
| 2 | Prince | Mumbai |
| 3 | Mohit | Bangalore |
| 4 | Rahul | 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 |

Table: Payments

| payment_id | order_id | amount |
| --- | --- | --- |
| 201 | 101 | 2500.00 |
| 202 | 102 | 1250.00 |
| 203 | 103 | 3000.00 |
| 204 | 104 | 1750.00 |
| 205 | 106 | 2200.00 |

Expected Output

| payment_id | order_id | name | amount |
| --- | --- | --- | --- |
| 201 | 101 | Atul | 2500.00 |
| 202 | 102 | Atul | 1250.00 |
| 203 | 103 | Prince | 3000.00 |
| 204 | 104 | Mohit | 1750.00 |
| 205 | NULL | NULL | 2200.00 |

• Return: payment_id, amount, order_id, customer_name
• Sort by payment_id ascending