Top Downtime Reasons by Region | Dataford Interview Questions

Tesla Operations uses service logs from Tesla Service Centers to identify the largest sources of downtime. Write a PostgreSQL query that returns the top three downtime reasons for the Pacific Northwest region.

Requirements

Join service logs to the regions table using region_id.
Consider only completed logs with a positive, non-null downtime_minutes value and a non-null downtime_reason.
Aggregate total downtime minutes and log count for each reason in the Pacific Northwest.
Rank the reasons by total downtime descending, breaking ties alphabetically, and return only ranks 1 through 3.

Schema

regions

Column	Type	Description
region_idPK	INTEGER	Unique operating region identifier
region_name	VARCHAR(100)	Tesla operating region name

service_logs

Column	Type	Description
log_idPK	INTEGER	Unique service log identifier
region_id	INTEGER	References regions.region_id
downtime_reason	VARCHAR(100)	Reason associated with the downtime
downtime_minutes	INTEGER	Duration of downtime in minutes
service_status	VARCHAR(30)	Status of the service record

Your question is Top Downtime Reasons by Region. 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