Write SQL and Python, run instantly in your browser, and track your progress.
You are a Logistics Analyst at Amazon. The Regional Operations team needs to analyze delivered order performance by shipping city to optimize distribution center staffing. Filter orders to only those with 'delivered' status, then calculate key metrics for each city.
| Column Name | Type |
|---|---|
| order_id | int64 |
| order_number | object |
| customer_id | int64 |
| order_datetime |
You are a Logistics Analyst at Amazon. The Regional Operations team needs to analyze delivered order performance by shipping city to optimize distribution center staffing. Filter orders to only those with 'delivered' status, then calculate key metrics for each city.
| Column Name | Type |
|---|---|
| order_id | int64 |
| order_number | object |
| customer_id | int64 |
| order_datetime |
| object |
| object |
| status | object |
| status | object |
| ship_city | object |
| ship_city | object |
| ship_state | object |
| ship_state | object |
| ship_country | object |
| ship_country | object |
| subtotal | float64 |
| subtotal | float64 |
| shipping_fee | float64 |
| shipping_fee | float64 |
| tax | float64 |
| tax | float64 |
| total_amount | float64 |
| total_amount | float64 |
| order_id | order_number | customer_id | order_datetime | status | ship_city | ship_state | ship_country | subtotal | shipping_fee | tax | total_amount |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ORD-10001 | 46 |
| order_id | order_number | customer_id | order_datetime | status | ship_city | ship_state | ship_country | subtotal | shipping_fee | tax | total_amount |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ORD-10001 | 46 |
| ship_city | order_count | total_revenue | avg_order_value |
|---|---|---|---|
| Bristol | 1 | 2034.68 | 2034.68 |
| Winnipeg | 1 | 1646.43 | 1646.43 |
| Chicago | 3 | 1205.04 | 401.68 |
| Ottawa | 1 | 909.84 | 909.84 |
| ship_city | order_count | total_revenue | avg_order_value |
|---|---|---|---|
| Bristol | 1 | 2034.68 | 2034.68 |
| Winnipeg | 1 | 1646.43 | 1646.43 |
| Chicago | 3 | 1205.04 | 401.68 |
| Ottawa | 1 | 909.84 | 909.84 |
Showing first 5 of 19 rows.
Showing first 5 of 19 rows.
1. Data Selection:
2. Aggregation:
3. Output:
1. Data Selection:
2. Aggregation:
3. Output:
| 2025-08-20 07:01:34 |
| 2025-08-20 07:01:34 |
| shipped |
| shipped |
| 0.0 |
| 0.0 |
| 4.36 |
| 4.36 |
| 55.32 |
| 55.32 |
| 2 | ORD-10002 | 19 | 2025-02-24 04:56:03 | delivered | Austin | TX | US | 601.53 | 5.99 | 54.96 | 662.48 |
| 2 | ORD-10002 | 19 | 2025-02-24 04:56:03 | delivered | Austin | TX | US | 601.53 | 5.99 | 54.96 | 662.48 |
| 3 | ORD-10003 | 21 | 2025-02-13 17:43:43 | shipped | London | ENG | UK | 68.26 | 14.99 | 5.41 | 88.66 |
| 3 | ORD-10003 | 21 | 2025-02-13 17:43:43 | shipped | London | ENG | UK | 68.26 | 14.99 | 5.41 | 88.66 |
| 4 | ORD-10004 | 8 | 2025-02-07 13:00:50 | shipped | Calgary | AB | CA | 290.99 | 5.0 | 26.88 | 322.87 |
| 4 | ORD-10004 | 8 | 2025-02-07 13:00:50 | shipped | Calgary | AB | CA | 290.99 | 5.0 | 26.88 | 322.87 |
| 5 | ORD-10005 | 8 | 2025-06-24 03:59:58 | packed | Edmonton | AB | CA | 569.61 | 5.0 | 54.73 | 629.34 |
| 5 | ORD-10005 | 8 | 2025-06-24 03:59:58 | packed | Edmonton | AB | CA | 569.61 | 5.0 | 54.73 | 629.34 |
| Perth | 1 | 798.54 | 798.54 |
| Perth | 1 | 798.54 | 798.54 |