SQL for Site Performance | Dataford Interview Questions

Write an SQL query to analyze monthly operational performance by site, joining production, downtime, and staffing tables to identify the top underperforming locations.

Calculate performance using units produced per staffed hour. Include downtime as a supporting operational metric, and return the two lowest-performing sites for each month. Treat missing downtime as zero and use site ID to break performance ties.

Output

One row per selected site and month with month_start, site_id, units_produced, downtime_hours, staffed_hours, units_per_staffed_hour, and underperformance_rank.
Include up to two sites per month, ordered by month_start, then underperformance_rank, then site_id.

Schema

production

Column	Type	Description
production_idPK	INT	Unique production record identifier
site_id	INT	Operational site identifier
production_date	DATE	Date of production activity
units_produced	INT	Number of units produced

downtime

Column	Type	Description
downtime_idPK	INT	Unique downtime record identifier
site_id	INT	Operational site identifier
downtime_date	DATE	Date downtime was recorded
downtime_hours	NUMERIC(8,2)	Hours of downtime recorded

staffing

Column	Type	Description
staffing_idPK	INT	Unique staffing record identifier
site_id	INT	Operational site identifier
staffing_date	DATE	Date staffed hours were recorded
staffed_hours	NUMERIC(8,2)	Total employee hours assigned

Your question is SQL for Site Performance. 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