Katkat
Back

Top 5 Most Efficient Vehicle Models

WaymoData Analyst · Technical Screen
MediumData AnalystTechnical ScreenData Manipulation (SQL/Python)

Last updated

This problem tests your ability to aggregate data from multiple tables, calculate performance metrics, and handle common SQL pitfalls like division by zero and NULLs.

Problem Statement

You are working with a fleet database for an autonomous vehicle company. You have two tables: trips and maintenance_logs.

Tables

  • trips (trip_id, vehicle_id, distance_miles, fuel_consumed_gallons)
  • maintenance_logs (log_id, vehicle_id, maintenance_cost)

Write a SQL query to identify the top 5 most efficient vehicle models. Efficiency is defined as total_miles_driven / total_fuel_consumed. Only include vehicles that have incurred at least one maintenance cost entry.

Requirements

  1. Calculate the efficiency for each vehicle_id.
  2. Handle potential division by zero errors if total_fuel_consumed is 0 or NULL.
  3. Return the vehicle_id and the calculated efficiency.
  4. Rank the vehicles by efficiency in descending order. If two vehicles have the same efficiency, rank them by vehicle_id in ascending order.

Constraints

  • distance_miles and fuel_consumed_gallons are non-negative.
  • If a vehicle has no fuel consumption data, treat its efficiency as 0.

Example

Input: trips:

trip_idvehicle_iddistance_milesfuel_consumed_gallons
1A11005
2A1505
3B22000

maintenance_logs:

log_idvehicle_idmaintenance_cost
101A1500

Output:

vehicle_idefficiency
A115

Explanation: Vehicle A1 drove 150 miles total with 10 gallons used (150/10 = 15). Vehicle B2 is excluded because it has no maintenance log. B2 would have been excluded anyway due to division by zero protection if needed.

Hints

Hint 1

Use an INNER JOIN or an IN clause to filter for vehicles that exist in the maintenance_logs table.

Hint 2

Use SUM() with a GROUP BY clause to aggregate the total miles and total fuel per vehicle before calculating the efficiency ratio.

Hint 3

Use a CASE statement or NULLIF() to safely handle cases where the total fuel consumption might be zero to prevent query execution errors.

Follow-up questions

  • How would your query change if you were asked to only consider vehicles with an above-average maintenance cost?
  • What impact would adding a 'date' column have on calculating efficiency over a rolling 30-day window?
  • If the 'trips' table contained millions of rows, what indexes would you suggest adding to optimize this query's performance?