Write SQL and Python, run instantly in your browser, and track your progress.
You are a Business Analyst at Target. The Finance team wants to understand how revenue is distributed across product categories and order statuses. Merge order items with products and orders, then create a pivot table showing total revenue (sum of line_subtotal) by category and status.
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity |
You are a Business Analyst at Target. The Finance team wants to understand how revenue is distributed across product categories and order statuses. Merge order items with products and orders, then create a pivot table showing total revenue (sum of line_subtotal) by category and status.
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity |
| int64 |
| int64 |
| unit_price | float64 |
| unit_price | float64 |
| line_subtotal | float64 |
| line_subtotal | float64 |
| Column Name | Type |
|---|---|
| product_id | int64 |
| product_name | object |
| category | object |
| price | float64 |
| Column Name | Type |
|---|---|
| product_id | int64 |
| product_name | object |
| category | object |
| price | float64 |
| Column Name | Type |
|---|---|
| order_id | int64 |
| status | object |
| Column Name | Type |
|---|---|
| order_id | int64 |
| status | object |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal |
|---|---|---|---|---|---|
| 1 | 101 | 1 | 2 | 29.99 | 59.98 |
| 2 | 101 | 2 | 1 |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal |
|---|---|---|---|---|---|
| 1 | 101 | 1 | 2 | 29.99 | 59.98 |
| 2 | 101 | 2 | 1 |
| product_id | product_name | category | price |
|---|---|---|---|
| 1 | Running Shoes | Sports | 29.99 |
| 2 | Bluetooth Speaker | Electronics | 149.99 |
| 3 | Yoga Mat | Sports |
| product_id | product_name | category | price |
|---|---|---|---|
| 1 | Running Shoes | Sports | 29.99 |
| 2 | Bluetooth Speaker | Electronics | 149.99 |
| 3 | Yoga Mat | Sports |
| order_id | status |
|---|---|
| 101 | delivered |
| 102 | shipped |
| 103 | canceled |
| order_id | status |
|---|---|
| 101 | delivered |
| 102 | shipped |
| 103 | canceled |
| category | canceled | confirmed | delivered | packed | partially_returned | placed | returned | shipped |
|---|---|---|---|---|---|---|---|---|
| Apparel | 54.14 | 598.44 | 1077.89 | 0 | 0 | 0 | 553.64 | 324.44 |
| Beauty | 132.86 |
| category | canceled | confirmed | delivered | packed | partially_returned | placed | returned | shipped |
|---|---|---|---|---|---|---|---|---|
| Apparel | 54.14 | 598.44 | 1077.89 | 0 | 0 | 0 | 553.64 | 324.44 |
| Beauty | 132.86 |
Showing first 5 of 8 rows.
Showing first 5 of 8 rows.
1. DataFrames:
2. Merging:
3. Reshaping:
4. Output:
1. DataFrames:
2. Merging:
3. Reshaping:
4. Output:
| 149.99 |
| 149.99 |
| 149.99 |
| 149.99 |
| 3 | 102 | 1 | 1 | 29.99 | 29.99 |
| 3 | 102 | 1 | 1 | 29.99 | 29.99 |
| 4 | 102 | 3 | 2 | 19.99 | 39.98 |
| 4 | 102 | 3 | 2 | 19.99 | 39.98 |
| 5 | 103 | 2 | 1 | 149.99 | 149.99 |
| 5 | 103 | 2 | 1 | 149.99 | 149.99 |
| 19.99 |
| 19.99 |
| 2119.78 |
| 2119.78 |
| 1606.96 |
| 1606.96 |
| 426.96 |
| 426.96 |
| 0 |
| 0 |
| 866.26 |
| 866.26 |
| 0 |
| 0 |
| 1114.39 |
| 1114.39 |
| Books | 0 | 405.80 | 834.60 | 0 | 352.54 | 0 | 447.56 | 899.33 |
| Books | 0 | 405.80 | 834.60 | 0 | 352.54 | 0 | 447.56 | 899.33 |
| Electronics | 527.80 | 864.76 | 814.25 | 1317.67 | 761.05 | 901.30 | 1226.19 | 981.80 |
| Electronics | 527.80 | 864.76 | 814.25 | 1317.67 | 761.05 | 901.30 | 1226.19 | 981.80 |
| Home | 0 | 434.88 | 1040.70 | 969.81 | 451.98 | 31.21 | 792.75 | 757.43 |
| Home | 0 | 434.88 | 1040.70 | 969.81 | 451.98 | 31.21 | 792.75 | 757.43 |