Rank Top Vehicle Configurations

MediumSQL · PostgreSQL

Problem

Mercedes-Benz Group uses vehicle telemetry to compare configuration performance across development and fleet vehicles. Write a PostgreSQL query to identify the three best-performing configurations during January 2025.

Requirements

Join configurations, vehicles, and telemetry records.
Include telemetry recorded from 2025-01-01 through 2025-01-31, inclusive.
Use CTEs to calculate cumulative distance and energy consumption for each configuration, along with the number of contributing vehicles and telemetry records.
Use RANK() to rank configurations by cumulative distance in descending order, return ranks 1 through 3, and order the final result by rank.
Ignore configurations with fewer than two telemetry records in the period. Treat NULL energy readings as zero when calculating cumulative energy.

Schema

configurations

Column	Type	Description
configuration_idPK	INT	Configuration identifier
configuration_code	VARCHAR(20)	Mercedes-Benz configuration code
model_name	VARCHAR(80)	Vehicle model name
powertrain	VARCHAR(30)	Powertrain type

vehicles

Column	Type	Description
vehicle_idPK	INT	Vehicle identifier
configuration_id	INT	Referenced configuration
vin	VARCHAR(17)	Vehicle identification number
fleet_status	VARCHAR(20)	Current fleet status

telemetry

Column	Type	Description
telemetry_idPK	INT	Telemetry record identifier
vehicle_id	INT	Referenced vehicle
recorded_at	DATE	Date of telemetry capture
distance_km	DECIMAL(10,2)	Distance measured in kilometers
energy_consumed_kwh	DECIMAL(10,2)	Energy consumed in kilowatt-hours

Tablesconfigurationsvehiclestelemetry