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 ascendingservice_date, deriving service_date from the telemetry timestamp date.0 while preserving recorded zero and negative distances.weekly_miles as the vehicle's total for its Monday-through-Sunday calendar week.rolling_7_day_avg_miles across the current and previous six reported service dates, not calendar dates.rolling_7_day_avg_miles to exactly 2 decimal places.| 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 |