SQL Facilities Work Order Report | Dataford Interview Questions

Write a SQL query to report monthly facilities work order volume, average resolution time, and completion rate by building.

Use the work order creation month as the reporting month. A work order is complete when its status is Completed; average resolution time should be reported in hours for completed work orders only.

Output

One row per building and reporting month with at least one work order.
Columns: building_name, report_month, work_order_volume, avg_resolution_hours, and completion_rate_pct.
Sort by building_name, then report_month ascending.

Schema

buildings

Column	Type	Description
building_idPK	INT	Unique building identifier
building_name	VARCHAR(100)	University of Connecticut building name
campus	VARCHAR(50)	Campus where the building is located

work_orders

Column	Type	Description
work_order_idPK	INT	Unique facilities work order identifier
building_id	INT	Building associated with the work order
created_at	TIMESTAMP	Timestamp when the work order was created
completed_at	TIMESTAMP	Timestamp when the work order was completed
status	VARCHAR(30)	Current work order status
priority	VARCHAR(20)	Work order priority

Your question is SQL Facilities Work Order Report. 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