SQL for On-Time Shipment Rates | Dataford Interview Questions

Write a SQL query to identify the top 10 warehouses by on-time shipment rate over the last 90 days, including total shipments, on-time shipments, and on-time percentage.

Use the warehouses and shipments tables. Treat a shipment as on time when delivered_at is not null and is on or before promised_delivery_at.

Output

One row per qualifying warehouse, with warehouse_name, total_shipments, on_time_shipments, and on_time_percentage.
Include only shipments from the last 90 days and warehouses with at least one shipment.
Sort by on-time percentage descending, then total shipments descending, then warehouse ID ascending. Return no more than 10 rows.

Schema

warehouses

Column	Type	Description
warehouse_idPK	INT	Unique warehouse identifier
warehouse_name	VARCHAR(100)	Warehouse name
region	VARCHAR(50)	Operational region

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
warehouse_id	INT	Warehouse that handled the shipment
tracking_number	VARCHAR(30)	Shipment tracking number
shipment_date	DATE	Date the shipment left the warehouse
promised_delivery_at	DATE	Promised delivery date
delivered_at	DATE	Actual delivery date, or null if not delivered

Your question is SQL for On-Time Shipment Rates. 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