ISA-012 — Customers Without Orders
🟢 Beginner
⏱ 20 Minutes
⭐ 15 Points
🔓 Free
💻 SQL | PySpark
🏢 Retail Analytics
🎯 LEFT JOIN
Business Context
A retail company wants to identify customers who signed up but have never placed an order.
The marketing team plans to launch a special onboarding campaign targeting these customers.
The analytics team has been asked to generate a list of customers with no associated orders.
Business Impact
Identifying customers who have never purchased helps:
- Increase customer engagement
- Improve conversion rates
- Launch targeted campaigns
- Reduce customer churn
This is one of the most common reporting requests in analytics.
Dataset
customers
| customer_id | customer_name |
|---|---|
| 101 | John |
| 102 | Alice |
| 103 | Bob |
| 104 | Sarah |
| 105 | David |
orders
| order_id | customer_id | amount |
|---|---|---|
| O001 | 101 | 500 |
| O002 | 103 | 700 |
| O003 | 105 | 250 |
Task
Return only customers who have never placed an order.
Expected Output
| customer_id | customer_name |
|---|---|
| 102 | Alice |
| 104 | Sarah |
Constraints
- Include all customers.
- Identify customers without matching orders.
- Return only customers with no orders.
Supported Languages
✅ SQL
✅ PySpark
Data Engineering Pattern
This challenge teaches the:
Orphan Record Detection Pattern
Used to identify records that exist in one dataset but not another.
Concepts Tested
- LEFT JOIN
- NULL Handling
- Data Completeness
- Customer Analytics
Hint
Start with all customers.
Bring in order information.
Look for customers where no matching order exists.
Solution
🔒 Premium Solution
Premium members receive:
- SQL Solution
- PySpark Solution
- Step-by-Step Explanation
- Join Visualization
- Alternative Approaches