How would you use SQL window functions to compare daily, weekly, and rolling trends in autonomous vehicle telemetry at May Mobility?
Use the provided telemetry and vehicle records. Return trend metrics for active vehicles with telemetry recorded during the sample period.
vehicle_id, service_date, daily_miles, weekly_miles, rolling_7_day_avg_milesvehicle_id, then service_date ascending| Column | Type | Description |
|---|---|---|
| vehicle_idPK | INT | Unique May Mobility autonomous vehicle identifier |
| vehicle_name | VARCHAR(100) | Human-readable vehicle name |
| model | VARCHAR(50) | Vehicle model |
| status | VARCHAR(20) | Current operating status |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique telemetry event identifier |
| vehicle_id | INT | Vehicle associated with the telemetry event |
| recorded_at | TIMESTAMP | Timestamp when telemetry was recorded |
| distance_miles | DECIMAL(8,2) | Distance traveled during the telemetry interval |
| disengagement_count | INT | Number of autonomous-mode disengagements |
| route_name | VARCHAR(100) | May Mobility service route name |