SQL for Underperforming Work Centers | Dataford Interview Questions

Your question is SQL for Underperforming Work Centers. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Given a table of operational output, how would you write a query to identify the top three underperforming work centers over a specific period?

Use January 2025 as the period. Underperformance is measured as total target units minus total actual units, and NULL actual values should count as zero.

Output

Return one row per underperforming work center with work_center_id, work_center_name, total_target, total_output, and shortfall_units.
Include only work centers with a positive shortfall, ordered by shortfall_units descending, with work_center_id ascending as the tie-breaker.
Return only the top three rows.

Schema

operational_output

Column	Type	Description
output_idPK	INT	Unique operational output record identifier
work_center_id	INT	Identifier of the work center
work_center_name	VARCHAR(100)	Name of the work center
work_date	DATE	Date of the operational output
target_units	INT	Units targeted for the work center on the date
actual_units	INT	Units actually produced on the date