SQL: Top 5 Vehicle Models by Miles per kWh Under a Maintenance Cost Cap
Company: Waymo
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
```
Overview: Write a PostgreSQL query returning the top 5 vehicle models by miles per kWh, limited to models with total maintenance cost under 5000 and safe when energy is 0 or NULL. Tests aggregating two child tables without join fan-out and NULL-safe division.