SQL Rolling Defect Rate Query | Dataford Interview Questions

Mercedes-Benz Group operations teams need a daily quality metric across active manufacturing lines. Write a PostgreSQL query that calculates the overall defect rate for each production date and its rolling seven-day average.

Requirements

Include production from active lines only. Lines with is_active = FALSE or NULL must be excluded.
Calculate each daily defect rate as total defective units divided by total produced units across all active lines.
Calculate a rolling average over the current date and the preceding six calendar days using a window function.
Return dates in ascending order and round both rates to six decimal places.

Schema

manufacturing_lines

Column	Type	Description
line_idPK	INTEGER	Manufacturing line identifier
line_name	VARCHAR(100)	Mercedes-Benz production line name
plant_code	VARCHAR(20)	Manufacturing plant code
is_active	BOOLEAN	Whether the line is currently active

daily_production

Column	Type	Description
production_idPK	INTEGER	Production record identifier
line_id	INTEGER	Manufacturing line associated with the record
production_date	DATE	Production date
units_produced	INTEGER	Units produced on the line
defective_units	INTEGER	Units identified as defective
maintenance_note	TEXT	Optional production or maintenance note

Your question is SQL Rolling Defect Rate Query. 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