Moving Average Delivery Times | Dataford Interview Questions

How would you use window functions to calculate the moving average of delivery times over a week?

Use the delivery_orders table. Include completed deliveries with non-null delivery times, calculate results by delivery date, and treat the week as the current date plus the six preceding calendar days.

Output

One row per date represented by an eligible delivery.
Columns: delivery_date, daily_avg_delivery_minutes, and seven_day_moving_avg_minutes.
Round both averages to two decimal places and order by delivery_date ascending.

Schema

delivery_orders

Column	Type	Description
order_idPK	INT	Unique identifier for the delivery order
delivered_at	TIMESTAMP	Timestamp when the order was delivered
delivery_time_minutes	INT	Elapsed delivery time in minutes
delivery_status	VARCHAR(20)	Delivery lifecycle status

Your question is Moving Average Delivery Times. Start with the requirements and the one table 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

How would you use window functions to calculate the moving average of delivery times over a week?

Output

One row per date represented by an eligible delivery.
Columns: delivery_date, daily_avg_delivery_minutes, and seven_day_moving_avg_minutes.
Round both averages to two decimal places and order by delivery_date ascending.

Schema

delivery_orders

Column	Type	Description
order_idPK	INT	Unique identifier for the delivery order
delivered_at	TIMESTAMP	Timestamp when the order was delivered
delivery_time_minutes	INT	Elapsed delivery time in minutes
delivery_status	VARCHAR(20)	Delivery lifecycle status

Your question is Moving Average Delivery Times. Start with the requirements and the one table 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.