Top Server Components by Failures | Dataford Interview Questions

Supermicro's operations team wants to identify the best-performing server components for each month. Write a PostgreSQL query that calculates monthly failure rates while accounting for shipment volume, then returns the top three components per month.

Requirements

Aggregate shipped units and failed units by component and calendar month.
Include components with shipments but no recorded failures, treating their failed-unit count as zero.
Exclude months where total shipment volume is zero and rank components by the lowest failure rate, then highest shipment volume.
Return the top three ranked components for January and February 2025.

Schema

components

Column	Type	Description
component_idPK	INT	Unique component identifier
component_name	VARCHAR(100)	Supermicro component name
component_type	VARCHAR(50)	Component category
status	VARCHAR(20)	Component lifecycle status

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment record identifier
component_id	INT	Component included in the shipment
shipment_date	DATE	Date units were shipped
units_shipped	INT	Number of units shipped

failures

Column	Type	Description
failure_idPK	INT	Unique failure record identifier
component_id	INT	Component associated with the failure
failure_date	DATE	Date the failure was reported
units_failed	INT	Number of failed units in the record

Your question is Top Server Components by Failures. 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