SQL Query for City Performance | Dataford Interview Questions

Write a SQL query to track weekly order volume, average delivery time, and on-time rate by city and kitchen, and identify the top underperforming locations.

Use completed delivered orders only. Define a week using the Monday starting date, calculate delivery time in minutes, and include locations with at least two completed orders in a week. Return the two lowest-performing locations per week.

Output

One row per selected week, city, and kitchen.
Columns: week_start, city, kitchen_name, order_volume, avg_delivery_minutes, on_time_rate, and underperformance_rank.
On-time rate is a percentage where delivery at or before the promised time is on time.
Order by week_start, underperformance_rank, and kitchen_name.

Schema

cities

Column	Type	Description
city_idPK	INT	Unique city identifier
city_name	VARCHAR(100)	City name
state_code	VARCHAR(2)	Two-letter state code

kitchens

Column	Type	Description
kitchen_idPK	INT	Unique CloudKitchens kitchen identifier
city_id	INT	Owning city identifier
kitchen_name	VARCHAR(100)	Kitchen display name
active	BOOLEAN	Whether the kitchen is currently active

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
kitchen_id	INT	Kitchen fulfilling the order
placed_at	TIMESTAMP	Order placement timestamp
promised_at	TIMESTAMP	Promised delivery timestamp
delivered_at	TIMESTAMP	Actual delivery timestamp
status	VARCHAR(20)	Order lifecycle status

Your question is SQL Query for City Performance. Start with the requirements and the three 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