Top Failure Reasons by Region | Dataford Interview Questions

Your question is Top Failure Reasons by Region. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Verizon network operations teams use drop-event data to prioritize reliability investigations across service regions. Write a PostgreSQL query to identify the three most frequent failure reasons for each region.

Requirements

Join network_drop_events to verizon_regions using region_id.
Count only events whose event_status is FAILED and whose failure_reason is not NULL.
Rank reasons within each region by failure count descending, breaking ties alphabetically by reason, and return only ranks 1 through 3.
Return results ordered by region name, rank, and failure reason.

Schema

verizon_regions

Column	Type	Description
region_idPK	INT	Unique Verizon service-region identifier
region_name	VARCHAR(50)	Human-readable service-region name

network_drop_events

Column	Type	Description
event_idPK	INT	Unique network drop event identifier
region_id	INT	Region associated with the event
event_time	TIMESTAMP	Timestamp when the event occurred
failure_reason	VARCHAR(100)	Classified reason for the network failure
event_status	VARCHAR(20)	Lifecycle status of the event