Aggregate Drives From Telematics

HardSQL · PostgreSQL

Problem

Geotab analysts need a monthly view of completed fleet drives from telematics data. Write a PostgreSQL query that aggregates qualifying drives for active vehicles during Q1 2025.

Requirements

Exclude inactive vehicles, cancelled drives, and drives outside Q1 2025.
Return monthly drive count, total distance, average duration, and safety-event count for each vehicle.
Calculate safety events per 100 km, returning NULL when total distance is zero.
Use a window function to show each vehicle's prior reported monthly distance, percentage change, and rank within its region and month.
Include vehicles with no safety events through an appropriate join, and order results by region, month, and regional rank.

Schema

vehicles

Column	Type	Description
vehicle_idPK	INT	Unique vehicle identifier
vehicle_name	VARCHAR(100)	Fleet vehicle name
region	VARCHAR(50)	Operating region
status	VARCHAR(20)	Vehicle lifecycle status

drives

Column	Type	Description
drive_idPK	INT	Unique drive identifier
vehicle_id	INT	Vehicle associated with the drive
started_at	TIMESTAMP	Drive start timestamp
ended_at	TIMESTAMP	Drive end timestamp
distance_km	NUMERIC(10,2)	Distance traveled in kilometers
drive_status	VARCHAR(20)	Drive processing status

safety_events

Column	Type	Description
event_idPK	INT	Unique safety-event identifier
drive_id	INT	Drive associated with the event
event_type	VARCHAR(40)	Safety event classification

Tablesvehiclesdrivessafety_events