Top Failing Components Query | Dataford Interview Questions

Write a SQL query to identify the top five most frequently failing hardware components from a telemetry log table, grouped by region.

Use the telemetry_log table and consider only rows whose status indicates a failure. Exclude records with missing regions or component names.

Output

One row per ranked component within each region.
Columns: region, component_name, failure_count, and component_rank.
Return up to five components per region, ordered by region and rank. Break frequency ties alphabetically by component name.

telemetry_log

Column	Type	Description
log_idPK	INT	Unique telemetry log identifier
region	VARCHAR(50)	Regional location associated with the event
component_name	VARCHAR(100)	Hardware component associated with the event
event_status	VARCHAR(20)	Telemetry event status, such as FAILURE, SUCCESS, or WARNING
logged_at	TIMESTAMP	Timestamp when the telemetry event was recorded

Your question is Top Failing Components Query. Start with the requirements and the one table 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.

Write a SQL query to identify the top five most frequently failing hardware components from a telemetry log table, grouped by region.

Use the telemetry_log table and consider only rows whose status indicates a failure. Exclude records with missing regions or component names.

One row per ranked component within each region.
Columns: region, component_name, failure_count, and component_rank.
Return up to five components per region, ordered by region and rank. Break frequency ties alphabetically by component name.

telemetry_log

Column	Type	Description
log_idPK	INT	Unique telemetry log identifier
region	VARCHAR(50)	Regional location associated with the event
component_name	VARCHAR(100)	Hardware component associated with the event
event_status	VARCHAR(20)	Telemetry event status, such as FAILURE, SUCCESS, or WARNING
logged_at	TIMESTAMP	Timestamp when the telemetry event was recorded

Your question is Top Failing Components Query. Start with the requirements and the one table 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.