Top Facilities by Maintenance Spend | Dataford Interview Questions

A facilities team wants to identify locations with the highest recent maintenance costs. Write a SQL query to calculate recent maintenance spend, work-order volume, and average resolution time for each facility.

Use September 15, 2026 as the reporting date and consider work orders opened during the preceding 90 days.

Output

One row per facility, including facilities with no qualifying work orders.
Columns: facility_id, facility_name, total_maintenance_spend, work_order_count, and avg_resolution_hours.
Include only the five facilities with the highest total maintenance spend, ordered by spend descending and facility_id ascending as the tie-breaker.
Null maintenance costs contribute nothing to spend. Unresolved work orders remain in the count but are excluded from the average resolution time.

Schema

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(150)	Facility name

work_orders

Column	Type	Description
work_order_idPK	INT	Unique work order identifier
facility_id	INT	Facility where maintenance occurred
maintenance_cost	NUMERIC(12,2)	Maintenance spend for the work order
opened_at	TIMESTAMP	Timestamp when the work order was opened
resolved_at	TIMESTAMP	Timestamp when the work order was resolved

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