Top 3 Per Category SQL | Dataford Interview Questions

Cotality Property Insights needs a ranked view of active properties for market analysis. Write a PostgreSQL query that returns the top three properties by estimated market value within each active property category.

Requirements

Join property_listings to property_categories using category_id.
Include only active listings with a non-null estimated market value and active categories.
Rank properties independently within each category by estimated market value descending. Break ties with listing_id ascending.
Return only ranks 1 through 3, ordered by category name and rank.

Schema

property_categories

Column	Type	Description
category_idPK	INT	Unique property category identifier
category_name	VARCHAR(100)	Display name of the property category
is_active	BOOLEAN	Whether the category is active in Cotality Property Insights

property_listings

Column	Type	Description
listing_idPK	INT	Unique property listing identifier
category_id	INT	Referenced property category identifier
address	VARCHAR(200)	Property street address
estimated_market_value	NUMERIC(12,2)	Estimated market value of the property
valuation_date	DATE	Date on which the estimate was produced
listing_status	VARCHAR(30)	Current listing status

Your question is Top 3 Per Category SQL. 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