Write SQL and Python, run instantly in your browser, and track your progress.
You are a Data Analyst at Amazon. The Operations team needs a detailed report showing order items with both order status and product names. Chain multiple merges to combine order_items with orders and products DataFrames.
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity | int64 |
You are a Data Analyst at Amazon. The Operations team needs a detailed report showing order items with both order status and product names. Chain multiple merges to combine order_items with orders and products DataFrames.
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity | int64 |
| unit_price | float64 |
| unit_price | float64 |
| line_subtotal | float64 |
| line_subtotal | float64 |
| 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 |
| Column Name | Type |
|---|---|
| product_id | int64 |
| product_name | object |
| category | object |
| price | float64 |
| stock | int64 |
| Column Name | Type |
|---|---|
| product_id | int64 |
| product_name | object |
| category | object |
| price | float64 |
| stock | int64 |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal |
|---|---|---|---|---|---|
| 1 | 100 | 5 | 2 | 29.99 | 59.98 |
| 2 | 100 | 8 | 1 |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal |
|---|---|---|---|---|---|
| 1 | 100 | 5 | 2 | 29.99 | 59.98 |
| 2 | 100 | 8 | 1 |
| order_id | order_number | customer_id | order_datetime | status | total_amount | payment_status |
|---|---|---|---|---|---|---|
| 100 | ORD-100 | 1 | 2025-01-15 | shipped | 79.97 | captured |
| 101 |
| order_id | order_number | customer_id | order_datetime | status | total_amount | payment_status |
|---|---|---|---|---|---|---|
| 100 | ORD-100 | 1 | 2025-01-15 | shipped | 79.97 | captured |
| 101 |
| product_id | product_name | category | price | stock |
|---|---|---|---|---|
| 5 | Running Shoes | Sports | 29.99 | 100 |
| 8 | Yoga Mat | Sports | 19.99 | 75 |
| product_id | product_name | category | price | stock |
|---|---|---|---|---|
| 5 | Running Shoes | Sports | 29.99 | 100 |
| 8 | Yoga Mat | Sports | 19.99 | 75 |
| order_item_id | order_number | status | product_name | quantity | line_subtotal |
|---|---|---|---|---|---|
| 1 | ORD-10001 | shipped | Xbox Wireless Controller | 1 | 50.96 |
| 2 | ORD-10002 | delivered | Hyperice Hypervolt 2 Pro | 3 | 601.53 |
| 3 | ORD-10003 |
| order_item_id | order_number | status | product_name | quantity | line_subtotal |
|---|---|---|---|---|---|
| 1 | ORD-10001 | shipped | Xbox Wireless Controller | 1 | 50.96 |
| 2 | ORD-10002 | delivered | Hyperice Hypervolt 2 Pro | 3 | 601.53 |
| 3 | ORD-10003 |
Showing first 5 of 123 rows.
Showing first 5 of 123 rows.
1. DataFrames:
2. Merge:
3. Output:
1. DataFrames:
2. Merge:
3. Output:
| payment_status | object |
| payment_status | object |
| 19.99 |
| 19.99 |
| 19.99 |
| 19.99 |
| 3 | 101 | 12 | 1 | 149.99 | 149.99 |
| 3 | 101 | 12 | 1 | 149.99 | 149.99 |
| ORD-101 |
| ORD-101 |
| 2 |
| 2 |
| 2025-01-16 |
| 2025-01-16 |
| delivered |
| delivered |
| 149.99 |
| 149.99 |
| captured |
| captured |
| 12 |
| 12 |
| Bluetooth Speaker |
| Bluetooth Speaker |
| Electronics |
| Electronics |
| 149.99 |
| 149.99 |
| 50 |
| 50 |
| shipped |
| shipped |
| All-Clad D3 Stainless 10-Piece Set |
| All-Clad D3 Stainless 10-Piece Set |
| 2 |
| 2 |
| 68.26 |
| 68.26 |
| 4 | ORD-10004 | shipped | Paula's Choice 2% BHA Exfoliant | 1 | 290.99 |
| 4 | ORD-10004 | shipped | Paula's Choice 2% BHA Exfoliant | 1 | 290.99 |
| 5 | ORD-10005 | packed | Ring Video Doorbell Pro 2 | 3 | 569.61 |
| 5 | ORD-10005 | packed | Ring Video Doorbell Pro 2 | 3 | 569.61 |