Rolling 7-Day Average Query | Dataford Interview Questions

Deliveroo’s operations team wants to monitor daily completed order volume without losing dates that had no completed orders. Write a PostgreSQL query that returns the daily order count and its rolling seven-day average.

Requirements

Count only orders with status = 'completed' and a matching restaurant.
Include every calendar date between the earliest and latest order dates, showing zero for dates without completed orders.
Calculate the average order volume for the current date and up to the six preceding calendar dates.
Return results in ascending date order, rounded to two decimal places.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
restaurant_id	INT	Restaurant associated with the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Current order status

restaurants

Column	Type	Description
restaurant_idPK	INT	Unique restaurant identifier
restaurant_name	VARCHAR(100)	Restaurant name
city	VARCHAR(80)	Restaurant city

Your question is Rolling 7-Day Average Query. 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