SQL Failure Modes by Region | Dataford Interview Questions

Given a dataset of equipment failure logs, write a SQL query to find the top 3 most frequent failure modes per region.

Use equipment_failure_logs as the source table. Exclude records with a missing region or failure mode. If failure modes have equal frequencies, order them alphabetically and include at most three modes per region.

Output

One row per selected region and failure mode.
Columns: region, failure_mode, failure_count, and failure_rank.
Sort by region alphabetically, then failure_rank ascending.

Schema

equipment_failure_logs

Column	Type	Description
log_idPK	INT	Unique failure log identifier
region	VARCHAR(50)	Operational region where the failure occurred
failure_mode	VARCHAR(100)	Observed equipment failure mode
failure_date	DATE	Date the failure was recorded

Your question is SQL Failure Modes by Region. 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.

Problem

Given a dataset of equipment failure logs, write a SQL query to find the top 3 most frequent failure modes per region.

Output

One row per selected region and failure mode.
Columns: region, failure_mode, failure_count, and failure_rank.
Sort by region alphabetically, then failure_rank ascending.

Schema

equipment_failure_logs

Column	Type	Description
log_idPK	INT	Unique failure log identifier
region	VARCHAR(50)	Operational region where the failure occurred
failure_mode	VARCHAR(100)	Observed equipment failure mode
failure_date	DATE	Date the failure was recorded

Your question is SQL Failure Modes by Region. 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.