Analyze Late Deliveries with SQL | Dataford Interview Questions

What SQL query would you use to analyze late deliveries by warehouse, day of week, and delivery window at Gopuff?

Use the provided delivery, warehouse, and delivery-window data. Treat a delivery as late when it arrives after its promised time, and exclude records without the timestamps or dimension values needed for this analysis.

Output

One row per warehouse, weekday, and delivery window with qualifying deliveries
Columns: warehouse_name, weekday_number, weekday_name, window_name, total_deliveries, late_deliveries, late_percentage
Order by warehouse name, weekday number, then window name

Schema

deliveries

Column	Type	Description
delivery_idPK	INT	Unique delivery identifier
warehouse_id	INT	Warehouse fulfilling the delivery
window_id	INT	Promised delivery-window identifier
promised_at	TIMESTAMP	Promised delivery timestamp
delivered_at	TIMESTAMP	Actual delivery timestamp

warehouses

Column	Type	Description
warehouse_idPK	INT	Unique warehouse identifier
warehouse_name	VARCHAR(100)	Gopuff warehouse name
city	VARCHAR(100)	Warehouse city

delivery_windows

Column	Type	Description
window_idPK	INT	Unique delivery-window identifier
window_name	VARCHAR(100)	Readable delivery-window name
window_start	TIME	Window start time
window_end	TIME	Window end time

Your question is Analyze Late Deliveries with SQL. 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