Your question is Compute Category Sales by Location. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Given sales, item category, and pricing tables, write a SQL query to calculate the total sales value for each item category and location during a specified period.
Use the period from January 1, 2024 through January 31, 2024, with the start date included and the end date excluded. Calculate sales value as quantity multiplied by the unit price valid on the sale date. Exclude sales without an item category or location.
item_category, location, and total_sales_value.item_category ascending, then location ascending.| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique sale identifier |
| item_id | INT | Sold item identifier |
| location | VARCHAR(50) | Sale location |
| sale_date | DATE | Date of sale |
| quantity | INT | Number of units sold |
| Column | Type | Description |
|---|---|---|
| item_idPK | INT | Unique item identifier |
| category_name | VARCHAR(100) | Item category name |
| Column | Type | Description |
|---|---|---|
| price_idPK | INT | Unique pricing record identifier |
| item_id | INT | Priced item identifier |
| unit_price | NUMERIC(12,2) | Unit selling price |
| effective_from | DATE | Inclusive start date for the price |
| effective_to | DATE | Exclusive end date for the price |