SQL: Top 5 Vehicle Models by Miles per kWh Under a Maintenance Cost Cap

This SQL question was asked live in a first-round screen for a business intelligence role, with follow-ups on how the query handles NULLs and how it could be optimized.

Return the top 5 most efficient vehicle models, where a model's efficiency is its total miles driven divided by its total energy consumed in kWh (miles per kWh), computed across all vehicles of that model. Only include models whose total maintenance cost, across all vehicles of that model, is less than 5000. The query must be production-ready: it must not fail when energy is 0 or NULL. Vehicles that have never had a maintenance record are part of the answer too; they contribute a maintenance cost of 0.

The interview provided its own schema, which was not preserved. Use the following schema for this practice version.

Input Tables

vehicles: one row per vehicle.

Column	Type	Meaning
vehicle_id	INTEGER	Primary key
model	TEXT	Vehicle model name; never NULL

trips: one row per trip.

Column	Type	Meaning
trip_id	INTEGER	Primary key
vehicle_id	INTEGER	References vehicles.vehicle_id
miles_driven	NUMERIC	Miles driven on the trip; may be NULL
energy_kwh	NUMERIC	Energy consumed on the trip in kWh; may be NULL or 0

maintenance: one row per maintenance event.

Column	Type	Meaning
maintenance_id	INTEGER	Primary key
vehicle_id	INTEGER	References vehicles.vehicle_id
cost	NUMERIC	Cost of the event; never NULL, never negative

Output Contract

• Write one read-only PostgreSQL query (a single SELECT, optionally with CTEs).
• For each model: total miles = sum of non-NULL miles_driven over all trips of its vehicles (0 if there are none); total energy = sum of non-NULL energy_kwh over the same trips; total maintenance = sum of cost over all maintenance rows of its vehicles (0 if there are none).
• A model is eligible when its total maintenance is less than 5000 and its total energy is greater than 0. Models whose total energy is 0 or NULL (including models with no trips) have no defined efficiency and are excluded.
• Return at most 5 rows with columns model (TEXT) and miles_per_kwh (NUMERIC, total miles divided by total energy, rounded to 2 decimal places).
• Rank by the unrounded efficiency descending, breaking ties by model ascending, and return rows in that order. If fewer than 5 models are eligible, return all of them.

Example

vehicles

vehicle_id	model
1	Aurora
2	Aurora
3	Breeze
4	Comet
5	Dart

trips

trip_id	vehicle_id	miles_driven	energy_kwh
1	1	100	25
2	1	60	15
3	2	40	10
4	3	90	20
5	3	NULL	5
6	4	120	0
7	4	30	NULL
8	5	300	60

maintenance

maintenance_id	vehicle_id	cost
1	1	1500
2	1	1500
3	2	1200
4	5	5200

• Aurora: 200 miles, 50 kWh, maintenance 4200: eligible, 4.00 miles per kWh.
• Breeze: 90 miles, 25 kWh, no maintenance records (0): eligible, 3.60.
• Comet: total energy 0: excluded.
• Dart: 5.00 miles per kWh, but maintenance 5200: excluded.

Expected result:

model	miles_per_kwh
Aurora	4.00
Breeze	3.60

Constraints and Clarifications

• Totals are per model, not per vehicle: compute miles and energy across all trips of all the model's vehicles before dividing.
• A trip with NULL miles_driven contributes nothing to miles but still contributes its energy_kwh, and the reverse.
• The maintenance threshold is strict: a total of exactly 5000 is excluded.
• model is unique per output row, and the tie rule makes the order total.

Hint: Count each fact once

Vehicle 1 has two trips and two maintenance events. Check how many times each of its miles and each of its costs would be added up by your joins.

Top 5 Vehicle Models by Miles per kWh Under a Maintenance Cost Cap

Table vehicles

column	type	constraints
vehicle_id	INTEGER	PRIMARY KEY
model	TEXT	NOT NULL

Sample data:

model	vehicle_id
Aurora	1
Aurora	2
Breeze	3
Breeze	4
Comet	5
Drift	6
Echo	7
Ember	8
Falcon	9
Falcon	10
Glide	11
Lumen	12
Nova	13
Vector	14
Zephyr	15

Table trips

column	type	constraints
trip_id	INTEGER	PRIMARY KEY
vehicle_id	INTEGER
miles_driven	NUMERIC
energy_kwh	NUMERIC

Sample data:

energy_kwh	miles_driven	trip_id	vehicle_id
30	120	1	1
20	80	2	1
30	100	3	2
40	189	4	3
5	NULL	5	3
0	12	6	3
0	120	7	5
NULL	30	8	5
NULL	50	9	6
NULL	40	10	6
22	100.04	11	7
18	80	12	7
36	160	13	9
NULL	20	14	9
14	45.2	15	10
20	NULL	16	11
12.5	NULL	17	11
18	100	18	12
12	60	19	12
24	90	20	13
16	60	21	13
30	174	22	14
30	186	23	15

Table maintenance

column	type	constraints
maintenance_id	INTEGER	PRIMARY KEY
vehicle_id	INTEGER
cost	NUMERIC	NOT NULL

Sample data:

cost	maintenance_id	vehicle_id
1500	1	1
1300	2	1
1200	3	2
400	4	5
250	5	6
1850	6	7
800	7	8
2100	8	9
950	9	10
300	10	11
3200	11	12
1799.99	12	12
900	13	13
650	14	13
2500	15	14
2500	16	14
3100	17	15
2500	18	15

Expected output

model	miles_per_kwh
Lumen	5.33
Falcon	4.5
Echo	4.5
Breeze	4.47
Aurora	3.75

Hints

1. Count each fact once: vehicle 1 has two trips and two maintenance events. Check how many times each of its miles and each of its costs would be added up by your joins.
2. Totals are per model, not per vehicle: add up miles and energy across all trips of all the model's vehicles before dividing.
3. Vehicles with no maintenance records still count, with a maintenance cost of 0, and a model whose total energy is 0 or NULL has no defined efficiency.