Rolling 7-Day Incident Average | Dataford Interview Questions

Samsara's fleet operations team wants to monitor incident trends for the West Operations team. Write a PostgreSQL query that calculates the daily incident count and its rolling 7-day average from January 1 through January 10, 2025.

Requirements

Include every calendar date in the requested range, including dates with zero incidents.
Count only incidents associated with active vehicles assigned to the West Operations team.
Use a window function to calculate the average of the current day and previous six calendar days.
Return the team name, report date, daily incident count, and rolling average, ordered chronologically.

Schema

teams

Column	Type	Description
team_idPK	INTEGER	Unique operations team identifier
team_name	VARCHAR(100)	Operations team name

vehicles

Column	Type	Description
vehicle_idPK	INTEGER	Unique vehicle identifier
team_id	INTEGER	Team assigned to the vehicle
vehicle_label	VARCHAR(100)	Fleet label displayed in Samsara
is_active	BOOLEAN	Whether the vehicle is active

incident_reports

Column	Type	Description
incident_idPK	INTEGER	Unique incident report identifier
vehicle_id	INTEGER	Vehicle associated with the incident
reported_at	TIMESTAMP	Timestamp when the incident was reported
incident_type	VARCHAR(100)	Incident category
severity	VARCHAR(20)	Incident severity

Your question is Rolling 7-Day Incident Average. Start with the requirements and the three 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