SQL for Occupancy and Delinquency | Dataford Interview Questions

Write a SQL query to calculate monthly occupancy rate, average rent, and delinquency rate by property from lease and payment tables.

Use January through March 2024 and evaluate lease occupancy on the first day of each month. Treat a payment with a NULL paid date as delinquent.

Output

One row per property and month, including properties with no active leases or payments
Columns: property_name, month, occupancy_rate_pct, average_rent, and delinquency_rate_pct
Rates and rent must be rounded to two decimal places
Order by property_name, then month ascending

properties

leases

Column	Type	Description
lease_idPK	INTEGER	Unique lease identifier
property_id	INTEGER	Referenced property
unit_id	VARCHAR(20)	Unit identifier
start_date	DATE	Lease start date
end_date	DATE	Lease end date, or NULL for an ongoing lease
monthly_rent	NUMERIC(10,2)	Monthly rent amount

payments

Column	Type	Description
payment_idPK	INTEGER	Unique payment identifier
lease_id	INTEGER	Referenced lease
due_date	DATE	Payment due date
paid_date	DATE	Date payment was made, or NULL if unpaid

Your question is SQL for Occupancy and Delinquency. 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.