Monthly Failure Rates by Component | Dataford Interview Questions

Supermicro’s hardware analytics team monitors component validation tests across server systems. Write a PostgreSQL query that calculates monthly failure rates by component and identifies the worst-performing components within each month.

Requirements

Include only completed tests with a test_status of PASS or FAILED; exclude inconclusive or missing statuses.
Calculate total completed tests, failed tests, and failure rate as failed_tests / total_tests * 100, rounded to two decimal places.
Rank components within each month from highest to lowest failure rate using a window function. Preserve equal ranks for equal rates.
Return results ordered by month, failure rate descending, and component ID.

Schema

component_catalog

Column	Type	Description
component_idPK	INT	Unique component identifier
component_name	VARCHAR(80)	Supermicro component name
component_family	VARCHAR(50)	Component category

component_test_events

Column	Type	Description
test_idPK	INT	Unique validation test identifier
component_id	INT	Tested component identifier
tested_at	TIMESTAMP	Test completion timestamp
test_status	VARCHAR(20)	Validation result status

Your question is Monthly Failure Rates by Component. 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