SQL Top Delayed Distribution Centers | Dataford Interview Questions

Walmart's supply chain team wants to identify distribution centers with the highest average shipment delays during the previous calendar quarter. Write a SQL query that calculates the average recorded delay for each eligible distribution center and returns the three centers with the highest averages.

Output

One row per distribution center, with columns distribution_center_id, center_code, center_name, and average_delay_minutes
Include only shipments from the previous calendar quarter and ignore shipments whose delay is NULL
Return the three centers with the highest average delay, ordered from highest to lowest average; break ties by ascending distribution_center_id

Schema

distribution_centers

Column	Type	Description
distribution_center_idPK	INT	Unique distribution center identifier
center_code	VARCHAR(20)	Operational distribution center code
center_name	VARCHAR(100)	Distribution center name

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
distribution_center_id	INT	Distribution center handling the shipment
shipment_date	DATE	Shipment processing date
delay_minutes	DECIMAL(10,2)	Shipment delay in minutes, nullable when not recorded

Your question is SQL Top Delayed Distribution Centers. 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