SQL Yield Percentiles by State | Dataford Interview Questions

Write a SQL query to find the top 10% of properties by yield in each state, given a table of property valuations and monthly rental income. Calculate yield using the latest valuation and the annualized average monthly rental income. Exclude properties without a valid valuation or rental income.

Output

One row per qualifying property, with state, property_id, annual_yield_percent, valuation_amount, and avg_monthly_rental_income.
Include at least one property per state; include ties at the cutoff.
Order by state, descending annual_yield_percent, then property_id.

Schema

property_valuations

Column	Type	Description
valuation_idPK	INT	Unique valuation record identifier
property_id	INT	Property identifier
state	VARCHAR(2)	Two-letter property state code
valuation_amount	DECIMAL(12,2)	Property valuation amount
valuation_date	DATE	Date of valuation

monthly_rental_income

Column	Type	Description
rental_income_idPK	INT	Unique rental income record identifier
property_id	INT	Property identifier
income_month	DATE	First day of the income month
monthly_rental_income	DECIMAL(10,2)	Rental income recorded for the month

Your question is SQL Yield Percentiles by State. 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