Top Facilities Maintenance Costs | Dataford Interview Questions

Write a query to find the top three facilities by maintenance cost over the last quarter, partitioned by region.

For grading, treat the last quarter as April 1, 2025 through June 30, 2025. Include only facilities with the three highest totals in their region, with facility ID as the tie-breaker.

Output

One row per selected facility, with region_name, facility_name, total_maintenance_cost, and facility_rank.
Include up to three facilities per region, ordered by region_name, facility_rank, and facility_id.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique regional identifier
region_name	VARCHAR(100)	Region name

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
region_id	INT	Region containing the facility
facility_name	VARCHAR(150)	Facility name

maintenance_work_orders

Column	Type	Description
work_order_idPK	INT	Unique maintenance work order identifier
facility_id	INT	Facility receiving maintenance
maintenance_date	DATE	Date of the maintenance work
maintenance_cost	NUMERIC(12,2)	Cost of the maintenance work

Your question is Top Facilities Maintenance Costs. 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.

Problem