Rolling Disengagement Averages | Dataford Interview Questions

Write a SQL query using SQL window functions to compute rolling 7-day averages of disengagement rates across different vehicle cohorts and geofences.

Calculate rates from engagement-level records and include only records with valid cohort and geofence references.

Output

One row per vehicle cohort, geofence, and observed activity date
Columns: vehicle_cohort, geofence, activity_date, daily_disengagement_rate, and rolling_7_day_avg
Include the current date and the preceding six calendar days in each rolling period, averaging observed daily rates
Order by vehicle_cohort, geofence, and activity_date ascending

engagement_events

Column	Type	Description
engagement_idPK	INT	Unique engagement record identifier
cohort_id	INT	Vehicle cohort reference
geofence_id	INT	Geofence reference
activity_date	DATE	Date of the engagement
disengaged	BOOLEAN	Whether the vehicle disengaged during the engagement

vehicle_cohorts

Column	Type	Description
cohort_idPK	INT	Unique vehicle cohort identifier
cohort_name	VARCHAR(100)	Vehicle cohort label

geofences

Column	Type	Description
geofence_idPK	INT	Unique geofence identifier
geofence_name	VARCHAR(100)	Geofence label

Your question is Rolling Disengagement Averages. 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.