Write SQL and Python, run instantly in your browser, and track your progress.
You are a Data Analyst at Shopify. The Customer Success team needs a customer lifetime value (CLV) report showing each customer's total spending, order count, and average order value. Combine data from customers and orders, then calculate these metrics.
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| object | |
| city | object |
You are a Data Analyst at Shopify. The Customer Success team needs a customer lifetime value (CLV) report showing each customer's total spending, order count, and average order value. Combine data from customers and orders, then calculate these metrics.
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| object | |
| city | object |
| state | object |
| state | object |
| country | object |
| country | object |
| created_at | object |
| created_at | object |
| Column Name | Type |
|---|---|
| order_id | int64 |
| order_number | object |
| customer_id | int64 |
| order_datetime | object |
| status | object |
| total_amount | float64 |
| Column Name | Type |
|---|---|
| order_id | int64 |
| order_number | object |
| customer_id | int64 |
| order_datetime | object |
| status | object |
| total_amount | float64 |
| customer_id | full_name | city | state | country | created_at | |
|---|---|---|---|---|---|---|
| 1 | Alice Johnson | alice@email.com | Seattle | WA | US | 2024-01-15 |
| 2 |
| customer_id | full_name | city | state | country | created_at | |
|---|---|---|---|---|---|---|
| 1 | Alice Johnson | alice@email.com | Seattle | WA | US | 2024-01-15 |
| 2 |
| order_id | order_number | customer_id | order_datetime | status | total_amount | payment_status |
|---|---|---|---|---|---|---|
| 1 | ORD-001 | 1 | 2024-06-15 | delivered | 150.00 | captured |
| 2 |
| order_id | order_number | customer_id | order_datetime | status | total_amount | payment_status |
|---|---|---|---|---|---|---|
| 1 | ORD-001 | 1 | 2024-06-15 | delivered | 150.00 | captured |
| 2 |
| customer_id | full_name | total_spent | order_count | avg_order_value |
|---|---|---|---|---|
| 50 | Brandon Zhou | 3697.97 | 5 | 739.59 |
| 8 | Patrick Turner | 3611.42 | 4 | 902.86 |
| 46 | Albert Nakamura | 3076.26 | 3 | 1025.42 |
| customer_id | full_name | total_spent | order_count | avg_order_value |
|---|---|---|---|---|
| 50 | Brandon Zhou | 3697.97 | 5 | 739.59 |
| 8 | Patrick Turner | 3611.42 | 4 | 902.86 |
| 46 | Albert Nakamura | 3076.26 | 3 | 1025.42 |
Showing first 5 of 50 rows.
Showing first 5 of 50 rows.
1. DataFrames:
2. Merge and Aggregate:
3. Handle Zero Orders:
4. Output:
1. DataFrames:
2. Merge and Aggregate:
3. Handle Zero Orders:
4. Output:
| payment_status | object |
| payment_status | object |
| Bob Smith |
| Bob Smith |
| bob@email.com |
| bob@email.com |
| Austin |
| Austin |
| TX |
| TX |
| US |
| US |
| 2024-02-20 |
| 2024-02-20 |
| 3 | Carol White | carol@email.com | Denver | CO | US | 2024-03-10 |
| 3 | Carol White | carol@email.com | Denver | CO | US | 2024-03-10 |
| ORD-002 |
| ORD-002 |
| 1 |
| 1 |
| 2024-07-20 |
| 2024-07-20 |
| delivered |
| delivered |
| 225.50 |
| 225.50 |
| captured |
| captured |
| 3 | ORD-003 | 2 | 2024-08-10 | delivered | 89.99 | captured |
| 3 | ORD-003 | 2 | 2024-08-10 | delivered | 89.99 | captured |
| 4 | ORD-004 | 1 | 2024-09-05 | shipped | 175.00 | captured |
| 4 | ORD-004 | 1 | 2024-09-05 | shipped | 175.00 | captured |
| 48 | Tyler Carter | 2912.23 | 2 | 1456.12 |
| 48 | Tyler Carter | 2912.23 | 2 | 1456.12 |
| 47 | Shirley Martinez | 2622.12 | 5 | 524.42 |
| 47 | Shirley Martinez | 2622.12 | 5 | 524.42 |