Top Yield Batches by Fab | Dataford Interview Questions

Write a SQL query using window functions to identify the top three highest-yielding manufacturing batches for each fab location over the last quarter.

For this exercise, treat the last quarter as April 1, 2026 through June 30, 2026. Calculate yield as good units divided by tested units, exclude incomplete or invalid yield records, and use batch ID as the tie-breaker.

Output

One row per selected batch, with fab_location, batch_id, completed_at, yield_rate, and yield_rank.
Include no more than three batches per fab location, ordered by location and yield rank.

Schema

fab_locations

Column	Type	Description
fab_codePK	VARCHAR(10)	Unique fab code
location_name	VARCHAR(100)	Fab location name
region	VARCHAR(50)	Geographic region

production_batches

Column	Type	Description
batch_idPK	INT	Unique manufacturing batch identifier
fab_code	VARCHAR(10)	Fab where the batch was produced
completed_at	DATE	Batch completion date
batch_status	VARCHAR(20)	Production status

batch_metrics

Column	Type	Description
batch_idPK	INT	Manufacturing batch identifier
tested_units	INT	Units tested for the batch
good_units	INT	Units passing quality tests

Your question is Top Yield Batches by Fab. 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