Write SQL and Python, run instantly in your browser, and track your progress.
You are a Customer Analytics Manager at Amazon. The Marketing team wants to identify high-value customers based on their delivered order history. Merge order data with customer information and calculate spending metrics for each customer.
| Column Name | Type |
|---|---|
| order_id | int64 |
| customer_id | int64 |
| status | object |
| total_amount | float64 |
You are a Customer Analytics Manager at Amazon. The Marketing team wants to identify high-value customers based on their delivered order history. Merge order data with customer information and calculate spending metrics for each customer.
| Column Name | Type |
|---|---|
| order_id | int64 |
| customer_id | int64 |
| status | object |
| total_amount | float64 |
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| object | |
| city | object |
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| object | |
| city | object |
| order_id | customer_id | status | total_amount |
|---|---|---|---|
| 1 | 46 | shipped | 55.32 |
| 2 | 19 | delivered | 662.48 |
| 3 | 21 | shipped |
| order_id | customer_id | status | total_amount |
|---|---|---|---|
| 1 | 46 | shipped | 55.32 |
| 2 | 19 | delivered | 662.48 |
| 3 | 21 | shipped |
| customer_id | full_name | city | |
|---|---|---|---|
| 1 | Sophia Lewis | sophia.lewis@gmail.com | San Diego |
| 2 | Marilyn Kelly | marilynkelly@yahoo.com | New York |
| 3 | Andrea Miller | andrea_miller@zoho.com |
| customer_id | full_name | city | |
|---|---|---|---|
| 1 | Sophia Lewis | sophia.lewis@gmail.com | San Diego |
| 2 | Marilyn Kelly | marilynkelly@yahoo.com | New York |
| 3 | Andrea Miller | andrea_miller@zoho.com |
| customer_id | full_name | total_orders | total_spent | avg_order_value |
|---|---|---|---|---|
| 50 | Brandon Zhou | 3 | 2818.19 | 939.40 |
| 43 | Gary Adams | 1 | 2034.68 | 2034.68 |
| 47 | Shirley Martinez | 3 | 1400.41 | 466.80 |
| customer_id | full_name | total_orders | total_spent | avg_order_value |
|---|---|---|---|---|
| 50 | Brandon Zhou | 3 | 2818.19 | 939.40 |
| 43 | Gary Adams | 1 | 2034.68 | 2034.68 |
| 47 | Shirley Martinez | 3 | 1400.41 | 466.80 |
Showing first 5 of 16 rows.
Showing first 5 of 16 rows.
1. Data Selection:
2. Join:
3. Aggregation:
4. Output:
1. Data Selection:
2. Join:
3. Aggregation:
4. Output:
| 88.66 |
| 88.66 |
| 4 | 8 | shipped | 322.87 |
| 4 | 8 | shipped | 322.87 |
| 5 | 8 | packed | 629.34 |
| 5 | 8 | packed | 629.34 |
| Winnipeg |
| Winnipeg |
| 8 | Luis Green | lgreen@aol.com | San Antonio |
| 8 | Luis Green | lgreen@aol.com | San Antonio |
| 19 | Anthony Alvarez | anthonya@protonmail.com | Austin |
| 19 | Anthony Alvarez | anthonya@protonmail.com | Austin |
| 36 | Sarah Morales | 2 | 1074.03 | 537.02 |
| 36 | Sarah Morales | 2 | 1074.03 | 537.02 |
| 42 | Isabella Hernandez | 2 | 884.22 | 442.11 |
| 42 | Isabella Hernandez | 2 | 884.22 | 442.11 |