SQL Average Selling Price | Dataford Interview Questions

CoStar Market Analytics stores product price periods and completed unit sales separately. Write a PostgreSQL query that calculates the average selling price for every product in the pricing table.

A sale matches a price when its purchase_date falls inclusively between start_date and end_date. The average must be weighted by units sold, not calculated as a simple average of listed prices.

Requirements

Return every product_id from prices, including products with no matching sales.
Calculate SUM(price * units) / SUM(units) for each product, rounded to two decimal places.
Return 0.00 when a product has no valid units sold, and order results by product_id ascending.
Do not count sales outside a product's active price period or sales for products absent from prices.

Schema

prices

Column	Type	Description
product_id	INT	Product identifier; multiple rows represent different price periods.
start_date	DATE	Inclusive beginning of the price period.
end_date	DATE	Inclusive end of the price period.
price	NUMERIC(10,2)	Listed selling price during the period.

units_sold

Column	Type	Description
sale_idPK	INT	Unique sale identifier.
product_id	INT	Product included in the sale.
purchase_date	DATE	Date on which the product was purchased.
units	INT	Number of units purchased.

Your question is SQL Average Selling Price. Start with the requirements and the two 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