Rank Defect Rates by Plant | Dataford Interview Questions

Write a query to rank daily manufacturing defect rates by factory plant using ranking window functions at Bosch.

Calculate each plant's daily defect rate as defective units divided by produced units, treating NULL defect counts as zero. Rank plants within each production date from the highest defect rate to the lowest, excluding dates with zero total production units.

Output

One row per plant and production date, with production_date, plant_id, plant_name, defect_rate_percent, and daily_rank.
Rank ties equally, with the next rank reflecting the number of tied rows.
Order by production_date, daily_rank, and plant_id.

Schema

plants

Column	Type	Description
plant_idPK	INT	Unique Bosch factory plant identifier
plant_code	VARCHAR(20)	Short plant code
plant_name	VARCHAR(100)	Factory plant name
country	VARCHAR(60)	Country where the plant operates

production_runs

Column	Type	Description
run_idPK	INT	Unique production run identifier
plant_id	INT	Plant responsible for the production run
production_date	DATE	Date of production
units_produced	INT	Number of units produced in the run
defects_count	INT	Number of defective units

Your question is Rank Defect Rates by Plant. 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