Optimize Large Historical Aggregation | Dataford Interview Questions

Optimize a slow-running query that aggregates millions of historical home price records at Redfin.

Using the provided schema, return monthly statistics for valid single-family home price records from 2024. The query should avoid unnecessary processing and include the previous available monthly average for each market.

Output

One row per market and month, with market_name, month_start, average_price, record_count, previous_month_average, and percent_change
Include only valid records from 2024 and markets with matching properties
Sort by market_name, then month_start ascending

Schema

home_price_history

Column	Type	Description
price_record_idPK	INT	Unique historical price record identifier
property_id	INT	Property associated with the price record
recorded_at	DATE	Date when the historical price was recorded
sale_price	NUMERIC(12,2)	Recorded home sale price

properties

Column	Type	Description
property_idPK	INT	Unique property identifier
market_id	INT	Market containing the property
property_type	VARCHAR(30)	Property classification
bedrooms	INT	Number of bedrooms

markets

Column	Type	Description
market_idPK	INT	Unique market identifier
market_name	VARCHAR(80)	Redfin market name
state_code	VARCHAR(2)	Two-letter state code

Your question is Optimize Large Historical Aggregation. 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