SQL Crash Metrics Over 30 Days | Dataford Interview Questions

Write a SQL query to extract and aggregate crash metrics from a highly nested operational vehicle dataset over a rolling 30-day window for AURORA.

Use the latest run date as the window endpoint. Safely handle missing, malformed, or scalar event payloads, and preserve groups whose mileage is NULL.

Output

One row per vehicle class, region, and metric date within the rolling window.
Columns: vehicle_class, region, metric_date, daily_miles, daily_crashes, daily_severe_crashes, rolling_30_day_miles, rolling_30_day_crashes, rolling_30_day_severe_crashes, and crashes_per_100k_miles.
Include NULL regions, order by vehicle class, region with NULLs last, and metric date.

Schema

fleet_vehicles

Column	Type	Description
vehicle_idPK	INT	Unique vehicle identifier
vehicle_class	VARCHAR(40)	Operational vehicle class
region	VARCHAR(40)	Fleet operating region

operational_runs

Column	Type	Description
run_idPK	INT	Unique operational run identifier
vehicle_id	INT	Vehicle used for the run
run_started_at	TIMESTAMP	Run start timestamp
operational_payload	JSONB	Nested mileage and event payload

Your question is SQL Crash Metrics Over 30 Days. 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