Total Sales by Category and Location | Dataford Interview Questions

Amazon Kuiper Commercial Services needs a sales summary for a reporting period. Write a PostgreSQL query that calculates the total sales value by item category and location from 2025-01-01 through 2025-01-31, inclusive.

Requirements

Join sales to item categories and effective-dated pricing.
Include only sales in the specified period and apply the price active on each sale date.
Aggregate quantity * unit_price by category and location, treating a NULL quantity as zero.
Exclude sales without a matching category or valid price, and order by location with NULL locations first, then category name.

Schema

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
item_id	INT	Sold item identifier
sale_date	DATE	Date on which the sale occurred
location	VARCHAR(100)	Commercial service location
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
item_id	INT	Item associated with the price
effective_from	DATE	Inclusive price start date
effective_to	DATE	Exclusive price end date, NULL indicates an active price
unit_price	NUMERIC(10,2)	Price per unit

Your question is Total Sales by Category and 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