Rolling Average for Telemetry | Dataford Interview Questions

Volkswagen Group Italia wants to monitor vehicle operating conditions from telemetry collected across its connected-vehicle fleet. Write a PostgreSQL query that calculates a time-based 30-day rolling average for each vehicle's battery temperature readings.

Requirements

Join telemetry_readings to vehicles and include the vehicle model in the output.
Return only battery_temperature readings, including rows where the reading value is NULL.
Calculate the average for the current reading and all readings for the same vehicle recorded during the preceding 30 days, using a window function ordered by recorded_at.
Return results ordered by vehicle ID and recording timestamp. Round the rolling average to two decimal places.

Schema

vehicles

Column	Type	Description
vehicle_idPK	INTEGER	Unique vehicle identifier
model	VARCHAR(80)	Volkswagen vehicle model
market	VARCHAR(40)	Sales market

telemetry_readings

Column	Type	Description
reading_idPK	INTEGER	Unique telemetry reading identifier
vehicle_id	INTEGER	Referenced vehicle identifier
metric_name	VARCHAR(50)	Telemetry metric name
reading_value	NUMERIC(8,2)	Measured metric value
recorded_at	TIMESTAMP	Time when the reading was captured

Your question is Rolling Average for Telemetry. 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