Write SQL and Python, run instantly in your browser, and track your progress.
You are a Business Intelligence Analyst at Shopify. The International team needs a comprehensive view of order metrics by shipping country. Create a pivot table showing both order count and total revenue by shipping country and order status, then reshape the result to have separate rows for each metric.
| Column Name | Type |
|---|---|
| order_id | int64 |
| ship_country | object |
| status | object |
| total_amount |
You are a Business Intelligence Analyst at Shopify. The International team needs a comprehensive view of order metrics by shipping country. Create a pivot table showing both order count and total revenue by shipping country and order status, then reshape the result to have separate rows for each metric.
| Column Name | Type |
|---|---|
| order_id | int64 |
| ship_country | object |
| status | object |
| total_amount |
| float64 |
| float64 |
| order_id | ship_country | status | total_amount |
|---|---|---|---|
| 1 | US | delivered | 249.99 |
| 2 | US | shipped | 89.50 |
| 3 | CA | delivered |
| order_id | ship_country | status | total_amount |
|---|---|---|---|
| 1 | US | delivered | 249.99 |
| 2 | US | shipped | 89.50 |
| 3 | CA | delivered |
| ship_country | metric | canceled | confirmed | delivered | packed | partially_returned | placed | returned | shipped |
|---|---|---|---|---|---|---|---|---|---|
| AU | order_count | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| ship_country | metric | canceled | confirmed | delivered | packed | partially_returned | placed | returned | shipped |
|---|---|---|---|---|---|---|---|---|---|
| AU | order_count | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
Showing first 5 of 12 rows.
Showing first 5 of 12 rows.
1. Data Selection:
2. Reshaping:
3. Output:
1. Data Selection:
2. Reshaping:
3. Output:
| 324.00 |
| 324.00 |
| 4 | US | canceled | 45.00 |
| 4 | US | canceled | 45.00 |
| 5 | UK | delivered | 189.99 |
| 5 | UK | delivered | 189.99 |
| 6 | CA | shipped | 125.00 |
| 6 | CA | shipped | 125.00 |
| 7 | US | delivered | 299.99 |
| 7 | US | delivered | 299.99 |
| AU | total_revenue | 0 | 0 | 798.54 | 0 | 0 | 0 | 0 | 0 |
| AU | total_revenue | 0 | 0 | 798.54 | 0 | 0 | 0 | 0 | 0 |
| CA | order_count | 0 | 0 | 4 | 1 | 1 | 0 | 0 | 2 |
| CA | order_count | 0 | 0 | 4 | 1 | 1 | 0 | 0 | 2 |
| CA | total_revenue | 0 | 0 | 2968.49 | 629.34 | 255.25 | 0 | 0 | 1382.81 |
| CA | total_revenue | 0 | 0 | 2968.49 | 629.34 | 255.25 | 0 | 0 | 1382.81 |
| DE | order_count | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 1 |
| DE | order_count | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 1 |