MyGeotab analysts need to monitor daily distance traveled by active vehicles. Write a PostgreSQL query that aggregates the fleet metric by reporting date and compares each date with the previous available reporting date.
vehicle_daily_metrics to geotab_vehicles and include only vehicles whose status is active.distance_km, treating NULL distance values as zero.LAG to return the previous day's total, the absolute change, and the percentage change. Return NULL for percentage change when there is no previous value or the previous value is zero.metric_date.| Column | Type | Description |
|---|---|---|
| vehicle_idPK | INT | Unique vehicle identifier |
| vehicle_name | VARCHAR(100) | Vehicle name shown in MyGeotab |
| region | VARCHAR(50) | Vehicle operating region |
| status | VARCHAR(20) | Current vehicle status |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INT | Unique daily metric identifier |
| vehicle_id | INT | Vehicle associated with the metric |
| metric_date | DATE | Date on which the metric was recorded |
| distance_km | DECIMAL(10,2) | Distance traveled in kilometers |