Write SQL and Python, run instantly in your browser, and track your progress.
You are a Customer Insights Analyst at Salesforce. The Sales team needs a comprehensive "Customer 360" view showing purchasing behavior for customers with delivered orders. Merge data from multiple tables to create a complete customer profile including spending, product diversity, and category preferences.
| Column Name | Type |
|---|---|
| order_id | int64 |
| customer_id | int64 |
| status | object |
| total_amount |
You are a Customer Insights Analyst at Salesforce. The Sales team needs a comprehensive "Customer 360" view showing purchasing behavior for customers with delivered orders. Merge data from multiple tables to create a complete customer profile including spending, product diversity, and category preferences.
| Column Name | Type |
|---|---|
| order_id | int64 |
| customer_id | int64 |
| status | object |
| total_amount |
| float64 |
| float64 |
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity | int64 |
| line_subtotal | float64 |
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity | int64 |
| line_subtotal | float64 |
| Column Name | Type |
|---|---|
| product_id | int64 |
| product_name | object |
| category | object |
| price | float64 |
| Column Name | Type |
|---|---|
| product_id | int64 |
| product_name | object |
| category | object |
| price | float64 |
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| city | object |
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| city | object |
| orders: order_id | customer_id | status | total_amount |
|---|---|---|---|
| 1 | 46 | shipped | 55.32 |
| 2 | 19 | delivered | 662.48 |
| orders: order_id | customer_id | status | total_amount |
|---|---|---|---|
| 1 | 46 | shipped | 55.32 |
| 2 | 19 | delivered | 662.48 |
| order_items: order_item_id | order_id | product_id | quantity | line_subtotal |
|---|---|---|---|---|
| 1 | 1 | 10 | 1 | 50.96 |
| 2 | 2 | 8 | 3 | 601.53 |
| order_items: order_item_id | order_id | product_id | quantity | line_subtotal |
|---|---|---|---|---|
| 1 | 1 | 10 | 1 | 50.96 |
| 2 | 2 | 8 | 3 | 601.53 |
| products: product_id | product_name | category | price |
|---|---|---|---|
| 8 | Dyson Supersonic Hair Dryer | Beauty | 429.99 |
| 10 | Casper Original Mattress Queen | Home | 1095.00 |
| products: product_id | product_name | category | price |
|---|---|---|---|
| 8 | Dyson Supersonic Hair Dryer | Beauty | 429.99 |
| 10 | Casper Original Mattress Queen | Home | 1095.00 |
| customers: customer_id | full_name | city |
|---|---|---|
| 19 | Anthony Alvarez | Austin |
| 46 | Sophia Lewis | San Diego |
| customers: customer_id | full_name | city |
|---|---|---|
| 19 | Anthony Alvarez | Austin |
| 46 | Sophia Lewis | San Diego |
| customer_id | full_name | city | order_count | total_items | total_spent | avg_order_value | unique_categories | unique_products |
|---|---|---|---|---|---|---|---|---|
| 50 | Brandon Zhou | Fort Worth | 3 | 6 | 2650.41 | 883.47 | 5 | 5 |
| 43 | Gary Adams |
| customer_id | full_name | city | order_count | total_items | total_spent | avg_order_value | unique_categories | unique_products |
|---|---|---|---|---|---|---|---|---|
| 50 | Brandon Zhou | Fort Worth | 3 | 6 | 2650.41 | 883.47 | 5 | 5 |
| 43 | Gary Adams |
Showing first 5 of 16 rows.
Showing first 5 of 16 rows.
1. Data Selection:
2. Joins:
3. Aggregation:
1. Data Selection:
2. Joins:
3. Aggregation:
| Miami |
| Miami |
| 1 |
| 1 |
| 4 |
| 4 |
| 1976.22 |
| 1976.22 |
| 1976.22 |
| 1976.22 |
| 4 |
| 4 |
| 4 |
| 4 |
| 47 | Shirley Martinez | Miami | 3 | 5 | 1293.84 | 431.28 | 5 | 5 |
| 47 | Shirley Martinez | Miami | 3 | 5 | 1293.84 | 431.28 | 5 | 5 |
| 36 | Sarah Morales | Minneapolis | 2 | 2 | 1002.51 | 501.26 | 2 | 2 |
| 36 | Sarah Morales | Minneapolis | 2 | 2 | 1002.51 | 501.26 | 2 | 2 |
| 42 | Isabella Hernandez | Vancouver | 2 | 3 | 882.76 | 441.38 | 2 | 3 |
| 42 | Isabella Hernandez | Vancouver | 2 | 3 | 882.76 | 441.38 | 2 | 3 |