Write SQL and Python, run instantly in your browser, and track your progress.
You are a Data Analyst at Costco. The Category Management team needs to see total revenue by product category. Merge order_items with products to get category information, then aggregate to calculate total revenue per category.
| Column Name | Type |
|---|---|
| order_item_id | int64 |
| order_id | int64 |
| product_id | int64 |
| quantity | int64 |
You are a Data Analyst at Costco. The Category Management team needs to see total revenue by product category. Merge order_items with products to get category information, then aggregate to calculate total revenue per category.
| 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 |
|---|---|
| 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 | 1 | 5 | 2 | 29.99 | 59.98 |
| 2 | 1 | 12 | 1 |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal |
|---|---|---|---|---|---|
| 1 | 1 | 5 | 2 | 29.99 | 59.98 |
| 2 | 1 | 12 | 1 |
| 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 |
| category | total_revenue | items_sold |
|---|---|---|
| Apparel | 2608.55 | 19 |
| Beauty | 6267.21 | 37 |
| Books | 2939.83 | 18 |
| Electronics | 7394.82 | 48 |
| Home | 4478.76 | 30 |
| category | total_revenue | items_sold |
|---|---|---|
| Apparel | 2608.55 | 19 |
| Beauty | 6267.21 | 37 |
| Books | 2939.83 | 18 |
| Electronics | 7394.82 | 48 |
| Home | 4478.76 | 30 |
Showing first 5 of 8 rows.
Showing first 5 of 8 rows.
1. DataFrames:
2. Merge:
3. Aggregation:
4. Output:
1. DataFrames:
2. Merge:
3. Aggregation:
4. Output:
| 149.99 |
| 149.99 |
| 149.99 |
| 149.99 |
| 3 | 2 | 5 | 1 | 29.99 | 29.99 |
| 3 | 2 | 5 | 1 | 29.99 | 29.99 |
| 4 | 2 | 8 | 3 | 19.99 | 59.97 |
| 4 | 2 | 8 | 3 | 19.99 | 59.97 |
| 12 |
| 12 |
| Bluetooth Speaker |
| Bluetooth Speaker |
| Electronics |
| Electronics |
| 149.99 |
| 149.99 |
| 50 |
| 50 |