Window Functions for Moving Averages | Dataford Interview Questions

Your question is Window Functions for Moving Averages. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Waymo One operations teams monitor rider wait times across operational territories. Write a PostgreSQL query that calculates a rolling average of daily completed-ride wait times for each territory.

Requirements

Join ride wait records to territory metadata and include only completed rides from August 1 through August 3, 2026.
Calculate the average wait time for each territory and service date. PostgreSQL AVG should ignore NULL wait values.
Use a window function to calculate the moving average across the current date and the two preceding reported dates within each territory.
Return territory ID, territory name, service date, daily average wait, and moving average wait, ordered by territory and date. Exclude territories with no matching qualifying ride records.

Schema

territories

Column	Type	Description
territory_idPK	INTEGER	Operational territory identifier
territory_name	VARCHAR(80)	Waymo One operational territory name

ride_wait_times

Column	Type	Description
wait_idPK	INTEGER	Wait-time record identifier
territory_id	INTEGER	Associated operational territory
service_date	DATE	Date of the ride
wait_minutes	NUMERIC(6,2)	Rider wait time in minutes
ride_status	VARCHAR(20)	Ride lifecycle status