Top Warehouse Bottleneck Identification | Dataford Interview Questions

How would you use SQL to identify the top bottleneck stations in a warehouse using throughput and queue-time data?

Use the warehouse_stations and station_metrics tables. Treat a station as a bottleneck when its average queue time is at least 20 minutes and its average throughput is below 100 units per hour.

Output

Return one row per qualifying station with bottleneck_rank, station_id, station_name, avg_throughput, avg_queue_minutes, and observation_count.
Include active stations only, rank by highest average queue time, then lowest average throughput, and use station_id as the tie-breaker.

Schema

warehouse_stations

Column	Type	Description
station_idPK	INT	Unique warehouse station identifier
station_name	VARCHAR(100)	Display name of the warehouse station
status	VARCHAR(20)	Operational status of the station

station_metrics

Column	Type	Description
metric_idPK	INT	Unique metric observation identifier
station_id	INT	Referenced warehouse station identifier
observation_date	DATE	Date of the operational observation
throughput_units_per_hour	INT	Units processed per hour
queue_minutes	DECIMAL(8,2)	Average queue time recorded for the observation

Your question is Top Warehouse Bottleneck Identification. 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