Rolling 7-Day Average in SQL | Dataford Interview Questions

Mercedes-Benz Group uses vehicle telemetry to monitor sensor anomalies across connected vehicles. Write a PostgreSQL query that calculates a calendar-aware rolling 7-day average of daily anomaly events for every registered vehicle.

Requirements

Include every registered vehicle and every calendar date between the earliest and latest event.
Count anomaly events per vehicle per day, treating days without events as zero.
Use a window function partitioned by vehicle_id to calculate the rolling average for the current date and preceding six dates.
Return results ordered by vehicle_id and report_date, with the average rounded to two decimal places.

Schema

vehicles

Column	Type	Description
vehicle_idPK	VARCHAR(20)	Unique Mercedes-Benz vehicle identifier
model	VARCHAR(80)	Vehicle model
market	VARCHAR(40)	Vehicle sales market

sensor_anomaly_events

Column	Type	Description
event_idPK	INTEGER	Unique anomaly event identifier
vehicle_id	VARCHAR(20)	Vehicle associated with the anomaly
event_timestamp	TIMESTAMP	Timestamp when the anomaly was recorded
anomaly_type	VARCHAR(50)	Anomaly classification, when available

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