SQL for Weekly Shipment Performance | Dataford Interview Questions

Write a SQL query to analyze weekly shipment performance by warehouse, including on-time rate, late shipments, and average transit time for Verkada.

Use shipped date to determine the week. Exclude undelivered shipments from punctuality metrics, and exclude shipments without a promised date from on-time calculations.

Output

One row per warehouse and shipment week.
Columns: warehouse_name, week_start, on_time_rate, late_shipments, and avg_transit_days.
Include only warehouses with shipments, order by warehouse name and week, and report rates and averages rounded to two decimal places.

Schema

warehouses

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

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
warehouse_id	INT	Warehouse that dispatched the shipment
shipped_at	DATE	Date the shipment left the warehouse
promised_at	DATE	Promised delivery date
delivered_at	DATE	Actual delivery date, or NULL if not delivered

Your question is SQL for Weekly Shipment Performance. 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