Compute Category Sales by Location | Dataford Interview Questions

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.

Output

One row per item category and location, with columns item_category, location, and total_sales_value.
Sort by item_category ascending, then location ascending.

Schema

sales

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

item_categories

Column	Type	Description
item_idPK	INT	Unique item identifier
category_name	VARCHAR(100)	Item category name

pricing

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

Your question is Compute Category Sales by Location. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem