Rolling Average of Late Deliveries | Dataford Interview Questions

HelloFresh operations analysts monitor weekly delivery reliability across fulfillment periods. Write a PostgreSQL query that calculates the percentage of late deliveries for each calendar week and a three-week rolling average of that percentage.

Requirements

Join calendar_weeks to deliveries using each delivery's delivery_date and the week's date range.
Calculate total deliveries, late deliveries, and the late-delivery percentage. A delivery is late when delivered_at is later than promised_at. Treat undelivered records as not yet classifiable and exclude them from the late count, but include them in total deliveries.
Include weeks with no deliveries, reporting a late-delivery rate of 0 for those weeks.
Use a window function to calculate the average late-delivery percentage for the current week and the two preceding weeks. Round percentage values to two decimal places.

Schema

deliveries

Column	Type	Description
delivery_idPK	INT	Unique delivery identifier
delivery_date	DATE	Date assigned to the delivery
promised_at	TIMESTAMP	Promised delivery timestamp
delivered_at	TIMESTAMP	Actual delivery timestamp
delivery_channel	VARCHAR(20)	Delivery channel

calendar_weeks

Column	Type	Description
week_startPK	DATE	Inclusive start of the reporting week
week_end	DATE	Exclusive end of the reporting week

Your question is Rolling Average of Late Deliveries. 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