SQL Window Neighborhood Comparison | Dataford Interview Questions

How would you use SQL window functions to compare each property's transaction price to the previous sale in the same neighborhood?

Use the properties and transactions tables. Order sales chronologically within each neighborhood, using transaction_id to break ties. If there is no previous sale or the relevant price is NULL, the comparison should be NULL.

Output

One row per recorded transaction with property, neighborhood, transaction, current price, previous sale price, and price difference.
Sort by neighborhood with NULL neighborhoods last, then transaction date and transaction ID.

Schema

properties

Column	Type	Description
property_idPK	INT	Unique property identifier
address	VARCHAR(150)	Property street address
neighborhood	VARCHAR(80)	Neighborhood containing the property

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
property_id	INT	Property involved in the transaction
transaction_date	DATE	Date of the property transaction
transaction_price	NUMERIC(12,2)	Recorded transaction price

Your question is SQL Window Neighborhood Comparison. 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