Advanced Joins: All Customers With Orders (Including No Orders)

SQL coding challenge · Difficulty: medium · +100 XP

Problem

Customer support needs a full order history linked to customer names. Show all customers — even those with no orders.

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

| customer_id | name | order_id | order_date |
| --- | --- | --- | --- |
| 1 | Atul | 101 | 2024-05-01 |
| 1 | Atul | 102 | 2024-05-05 |
| 2 | Prince | 103 | 2024-05-03 |
| 3 | Mohit | 104 | 2024-05-07 |
| 4 | Rahul | NULL | NULL |

• Return: name, email, order_id, order_date, amount
• Customers with no orders show NULL for order fields
• Sort by customer_id ascending