SQL for On-Time Delivery Rate | Dataford Interview Questions

Amazon DSP operations leaders need a daily view of delivery performance by delivery station. Write a PostgreSQL query that joins package delivery records to delivery routes and station information.

Requirements

Analyze delivered packages from 2025-02-10 through 2025-02-11, inclusive.
Return one row per station and route date with delivered package count, on-time delivery rate, and average delay in minutes.
Count a package as on time when delivered_at is less than or equal to promised_at. Early deliveries contribute zero minutes to average delay.
Exclude failed, pending, or undelivered packages, and order results by route date and station code.

Schema

stations

Column	Type	Description
station_idPK	INT	Primary key for the delivery station
station_code	VARCHAR(10)	Amazon DSP station code
station_name	VARCHAR(100)	Station display name

delivery_routes

Column	Type	Description
route_idPK	INT	Primary key for the delivery route
station_id	INT	References stations.station_id
route_date	DATE	Date on which the route operated
driver_name	VARCHAR(100)	Driver assigned to the route

package_deliveries

Column	Type	Description
delivery_idPK	INT	Primary key for the package delivery
route_id	INT	References delivery_routes.route_id
promised_at	TIMESTAMP	Promised delivery timestamp
delivered_at	TIMESTAMP	Actual delivery timestamp
status	VARCHAR(20)	Current package delivery status

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