Write SQL and Python, run instantly in your browser, and track your progress.
You are an Operations Analyst at Amazon. The Fulfillment team needs to understand order volume and value across different fulfillment statuses to optimize warehouse operations. Merge orders with order items and calculate metrics by fulfillment status.
| Column Name | Type |
|---|---|
| order_id | int64 |
| customer_id | int64 |
| status | object |
| total_amount | float64 |
You are an Operations Analyst at Amazon. The Fulfillment team needs to understand order volume and value across different fulfillment statuses to optimize warehouse operations. Merge orders with order items and calculate metrics by fulfillment status.
| Column Name | Type |
|---|---|
| order_id | int64 |
| customer_id | int64 |
| status | object |
| total_amount | float64 |
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity | int64 |
| line_subtotal | float64 |
| fulfillment_status | object |
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity | int64 |
| line_subtotal | float64 |
| fulfillment_status | 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 |
| order_item_id | order_id | product_id | quantity | line_subtotal | fulfillment_status |
|---|---|---|---|---|---|
| 1 | 1 | 10 | 1 | 50.96 | shipped |
| 2 | 2 | 8 |
| order_item_id | order_id | product_id | quantity | line_subtotal | fulfillment_status |
|---|---|---|---|---|---|
| 1 | 1 | 10 | 1 | 50.96 | shipped |
| 2 | 2 | 8 |
| fulfillment_status | order_count | item_count | total_quantity | total_value | avg_items_per_order |
|---|---|---|---|---|---|
| delivered | 27 | 40 | 84 | 12472.23 | 1.50 |
| shipped | 20 | 38 | 71 | 9766.34 | 1.90 |
| pending | 10 |
| fulfillment_status | order_count | item_count | total_quantity | total_value | avg_items_per_order |
|---|---|---|---|---|---|
| delivered | 27 | 40 | 84 | 12472.23 | 1.50 |
| shipped | 20 | 38 | 71 | 9766.34 | 1.90 |
| pending | 10 |
Showing first 5 of 7 rows.
Showing first 5 of 7 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 |
| 3 |
| 3 |
| 601.53 |
| 601.53 |
| delivered |
| delivered |
| 3 | 3 | 44 | 2 | 68.26 | shipped |
| 3 | 3 | 44 | 2 | 68.26 | shipped |
| 4 | 4 | 47 | 1 | 290.99 | shipped |
| 4 | 4 | 47 | 1 | 290.99 | shipped |
| 5 | 5 | 36 | 3 | 569.61 | backordered |
| 5 | 5 | 36 | 3 | 569.61 | backordered |
| 13 |
| 13 |
| 28 |
| 28 |
| 5946.47 |
| 5946.47 |
| 1.30 |
| 1.30 |
| returned | 6 | 9 | 20 | 4034.57 | 1.50 |
| returned | 6 | 9 | 20 | 4034.57 | 1.50 |
| backordered | 6 | 9 | 23 | 3992.85 | 1.50 |
| backordered | 6 | 9 | 23 | 3992.85 | 1.50 |