ISA-011 — Customer Orders Join
🟢 Beginner
⏱ 20 Minutes
⭐ 15 Points
🔓 Free
💻 SQL | PySpark
🏢 Retail Analytics
🎯 INNER JOIN
Business Context
A retail company stores customer information and order information in separate tables.
Business users need a report showing customer purchases with customer names attached to each order.
The analytics team has been asked to combine customer and order datasets into a single report for downstream dashboards.
Business Impact
Customer and order information often reside in different systems.
Combining datasets is one of the most common tasks performed by Data Engineers.
Joins power:
- Reporting
- Dashboards
- Analytics
- Data Warehouses
- Customer Insights
Without joins, meaningful business reporting is impossible.
Dataset
customers
| customer_id | customer_name |
|---|---|
| 101 | John |
| 102 | Alice |
| 103 | Bob |
orders
| order_id | customer_id | amount |
|---|---|---|
| O001 | 101 | 500 |
| O002 | 102 | 250 |
| O003 | 103 | 700 |
Task
Combine the customer and order datasets.
Return:
- customer_name
- order_id
- amount
Expected Output
| customer_name | order_id | amount |
|---|---|---|
| John | O001 | 500 |
| Alice | O002 | 250 |
| Bob | O003 | 700 |
Constraints
- Join using customer_id.
- Return one row per order.
- Include customer names.
- Return all matching records.
Supported Languages
✅ SQL
✅ PySpark
Concepts Tested
- INNER JOIN
- Primary Keys
- Foreign Keys
- Data Integration
Hint
Find the column that exists in both datasets and use it to combine the records.
Solution
🔒 Premium Solution
Premium members receive:
- SQL Solution
- PySpark Solution
- Step-by-Step Explanation
- Join Diagram
- Alternative Approaches