Week-over-Week Failure Rate Comparison | Dataford Interview Questions

Write a SQL query to compare current-week and previous-week failure rates by plant and machine type.

Use inspection_events, joined with plants and machine_types. Treat Monday as the first day of the week. A failure rate is failed inspections divided by all inspections in the relevant week. Include machine groups with activity in either week, and treat missing weekly activity as a 0% rate.

Output

One row per plant and machine type.
Columns: plant_name, machine_type, current_week_failure_rate, previous_week_failure_rate.
Rates should be percentages rounded to two decimals, ordered by plant name and machine type.

Schema

inspection_events

Column	Type	Description
inspection_idPK	INT	Unique inspection identifier
plant_id	INT	Plant where the inspection occurred
machine_type_id	INT	Machine type inspected
inspected_at	TIMESTAMP	Inspection timestamp
outcome	VARCHAR(20)	Inspection result, such as Passed or Failed

plants

Column	Type	Description
plant_idPK	INT	Unique plant identifier
plant_name	VARCHAR(100)	Plant name
region	VARCHAR(50)	Geographic region

machine_types

Column	Type	Description
machine_type_idPK	INT	Unique machine type identifier
machine_type_name	VARCHAR(100)	Machine type name
machine_family	VARCHAR(60)	Broad machine classification

Your question is Week-over-Week Failure Rate Comparison. 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