Rolling 30-Day Vehicle Average | Dataford Interview Questions

Polaris Industries wants to monitor usage trends across products such as Ranger, Sportsman, and RZR vehicles. Write a PostgreSQL query that calculates a rolling 30-day average of daily usage for each registered vehicle.

Requirements

Join registered vehicles to their usage records and restrict usage dates to 2025-01-01 through 2025-02-15.
Aggregate multiple usage records for the same vehicle and date into daily_miles.
Use a window function partitioned by vehicle and ordered by usage date to calculate the average of usage observations from the current date and preceding 29 calendar days. AVG should ignore NULL usage values.
Return results ordered by model and usage date.

Schema

vehicles

Column	Type	Description
vehicle_idPK	INT	Registered vehicle identifier
model	VARCHAR(50)	Polaris vehicle model
product_line	VARCHAR(50)	Polaris product family

vehicle_usage

Column	Type	Description
usage_idPK	INT	Usage record identifier
vehicle_id	INT	Referenced registered vehicle
usage_date	DATE	Date of the usage event
miles_used	NUMERIC(10,2)	Miles recorded during the event

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