SQL for Portfolio KPIs | Dataford Interview Questions

Write a SQL query to track monthly occupancy, delinquency, and work order volume by market and compare them to the prior month.

Use the provided market and property monthly metrics data. Include markets with recorded monthly metrics and compare each market's current month with its immediately preceding month.

Output

One row per market and month, ordered by market name and month.
Columns: market_name, month_start, occupancy_rate, delinquency_amount, work_order_volume, prior_occupancy_rate, prior_delinquency_amount, prior_work_order_volume, occupancy_change, delinquency_change, and work_order_change.
The first month for each market should have NULL prior-month values and NULL changes.

Schema

markets

Column	Type	Description
market_idPK	INT	Unique market identifier
market_name	VARCHAR(100)	Market display name
region	VARCHAR(50)	Broader operating region

property_monthly_metrics

Column	Type	Description
property_month_idPK	INT	Unique monthly property metric record
property_id	INT	Property identifier
market_id	INT	Market associated with the property
month_start	DATE	First day of the reporting month
total_units	INT	Total units represented by the property
occupied_units	INT	Occupied units during the month
delinquency_amount	DECIMAL(12,2)	Monthly delinquent rent amount
work_order_count	INT	Work orders opened during the month

Your question is SQL for Portfolio KPIs. 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