Write SQL and Python, run instantly in your browser, and track your progress.
You are a Data Engineer at Stripe. During a data quality audit, you need to find orphan records - orders that reference non-existent customers, and customers who have no orders. Use an outer join to identify these data integrity issues.
| Column Name | Type |
|---|---|
| order_id | int64 |
| order_number | object |
| customer_id | int64 |
| order_datetime | object |
You are a Data Engineer at Stripe. During a data quality audit, you need to find orphan records - orders that reference non-existent customers, and customers who have no orders. Use an outer join to identify these data integrity issues.
| Column Name | Type |
|---|---|
| order_id | int64 |
| order_number | object |
| customer_id | int64 |
| order_datetime | object |
| status | object |
| status | object |
| total_amount | float64 |
| total_amount | float64 |
| payment_status | object |
| payment_status | object |
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| object | |
| city | object |
| state | object |
| country | object |
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| object | |
| city | object |
| state | object |
| country | object |
| order_id | order_number | customer_id | order_datetime | status | total_amount | payment_status |
|---|---|---|---|---|---|---|
| 1 | ORD-001 | 1 | 2025-01-15 | delivered | 125.50 | captured |
| 2 |
| order_id | order_number | customer_id | order_datetime | status | total_amount | payment_status |
|---|---|---|---|---|---|---|
| 1 | ORD-001 | 1 | 2025-01-15 | delivered | 125.50 | captured |
| 2 |
| customer_id | full_name | city | state | country | created_at | |
|---|---|---|---|---|---|---|
| 1 | Alice Johnson | alice@email.com | Seattle | WA | US | 2024-06-01 |
| 2 |
| customer_id | full_name | city | state | country | created_at | |
|---|---|---|---|---|---|---|
| 1 | Alice Johnson | alice@email.com | Seattle | WA | US | 2024-06-01 |
| 2 |
| customer_id | full_name | order_id | issue_type |
|---|---|---|---|
| 4 | Luis Green | NaN | customer_no_orders |
| 5 | Anthony Alvarez | NaN | customer_no_orders |
| 7 | Jacob Howard | NaN | customer_no_orders |
| 9 | Ethan Ross | NaN | customer_no_orders |
| customer_id | full_name | order_id | issue_type |
|---|---|---|---|
| 4 | Luis Green | NaN | customer_no_orders |
| 5 | Anthony Alvarez | NaN | customer_no_orders |
| 7 | Jacob Howard | NaN | customer_no_orders |
| 9 | Ethan Ross | NaN | customer_no_orders |
Showing first 5 of 14 rows.
Showing first 5 of 14 rows.
1. DataFrames:
2. Merge:
3. Filter:
4. Output:
1. DataFrames:
2. Merge:
3. Filter:
4. Output:
| created_at | object |
| created_at | object |
| ORD-002 |
| ORD-002 |
| 2 |
| 2 |
| 2025-01-16 |
| 2025-01-16 |
| shipped |
| shipped |
| 89.99 |
| 89.99 |
| captured |
| captured |
| 3 | ORD-003 | 999 | 2025-01-17 | pending | 45.00 | pending |
| 3 | ORD-003 | 999 | 2025-01-17 | pending | 45.00 | pending |
| Bob Smith |
| Bob Smith |
| bob@email.com |
| bob@email.com |
| Portland |
| Portland |
| OR |
| OR |
| US |
| US |
| 2024-07-15 |
| 2024-07-15 |
| 3 | Carol White | carol@email.com | Denver | CO | US | 2024-08-20 |
| 3 | Carol White | carol@email.com | Denver | CO | US | 2024-08-20 |
| 13 | Keith White | NaN | customer_no_orders |
| 13 | Keith White | NaN | customer_no_orders |