Fleet Utilisation by Vehicle Type for One Week — SQL Problem
Fleet utilisation is the share of online time a vehicle spends on trips.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #312
Problem statement
Fleet utilisation is the share of online time a vehicle spends on trips. For the week 2024-08-05 to 2024-08-11 (inclusive), compute it per vehicle type over all that type's shifts: total on_trip_minutes ÷ total online_minutes × 100, rounded to 2 decimals (NULL if the type logged no online minutes).
Return vehicle_type, vehicles_online (distinct vehicles of that type with a shift in the week) and utilisation_pct, for every type with at least one shift in the week. Order by utilisation_pct descending, then vehicle_type ascending.
Tables
Table: FleetVehicle
| Column | Type |
|---|---|
| vehicle_id | int |
| registration_no | varchar |
| vehicle_type | enum(auto, bike, cab, ev_cab) |
Primary key: vehicle_id.
Table: VehicleShift
| Column | Type |
|---|---|
| shift_id | int |
| vehicle_id | int |
| shift_date | date |
| online_minutes | int |
| on_trip_minutes | int |
Primary key: shift_id.
One row per vehicle per shift; on_trip_minutes never exceeds online_minutes.
Examples
Example 1
FleetVehicle
| vehicle_id | registration_no | vehicle_type |
|---|---|---|
| 1 | KA01AB1001 | cab |
| 2 | KA01AB1002 | cab |
| 3 | KA05MN2001 | bike |
| 4 | KA03XY3001 | auto |
| 5 | KA51EV4001 | ev_cab |
VehicleShift
| shift_id | vehicle_id | shift_date | online_minutes | on_trip_minutes |
|---|---|---|---|---|
| 1 | 1 | 2024-08-05 | 600 | 420 |
| 2 | 1 | 2024-08-06 | 480 | 300 |
| 3 | 2 | 2024-08-11 | 540 | 270 |
| 4 | 3 | 2024-08-07 | 300 | 240 |
| 5 | 4 | 2024-08-04 | 600 | 500 |
| 6 | 4 | 2024-08-12 | 600 | 450 |
| 7 | 5 | 2024-08-08 | 0 | 0 |
| 8 | 3 | 2024-08-09 | 120 | 60 |
Output
| vehicle_type | vehicles_online | utilisation_pct |
|---|---|---|
| bike | 1 | 71.43 |
| cab | 2 | 61.11 |
| ev_cab | 1 | NULL |
How to solve Fleet Utilisation by Vehicle Type for One Week
Join shifts to their vehicles, keep the shifts dated in the week (BETWEEN on a date column includes both ends), and group by vehicle_type.
The utilisation of a type is a ratio of sums — total trip minutes over total online minutes — not the average of each shift's own ratio, which would give a 2-hour shift the same weight as a 10-hour one. Multiply by 100 before dividing, round to two decimals, and wrap the denominator in NULLIF(…, 0) so a type whose shifts logged zero online minutes yields NULL instead of a division error.
vehicles_online counts vehicles, not shifts, so it needs COUNT(DISTINCT s.vehicle_id). The alternative first sums per vehicle and then per type — the inner group makes each vehicle one row, so a plain COUNT(*) counts vehicles. In a descending sort MySQL places NULL last; spelling utilisation_pct IS NULL first makes that explicit. One scan of the week's shifts and a hash join to the vehicle table.
Reference solution (MySQL)
SELECT v.vehicle_type,
COUNT(DISTINCT s.vehicle_id) AS vehicles_online,
ROUND(100 * SUM(s.on_trip_minutes) / NULLIF(SUM(s.online_minutes), 0), 2) AS utilisation_pct
FROM FleetVehicle v
JOIN VehicleShift s ON s.vehicle_id = v.vehicle_id
WHERE s.shift_date BETWEEN '2024-08-05' AND '2024-08-11'
GROUP BY v.vehicle_type
ORDER BY utilisation_pct DESC, v.vehicle_typeAnother way
SELECT vehicle_type, COUNT(*) AS vehicles_online,
ROUND(100 * SUM(trip) / NULLIF(SUM(online), 0), 2) AS utilisation_pct
FROM (
SELECT v.vehicle_type, s.vehicle_id, SUM(s.online_minutes) AS online, SUM(s.on_trip_minutes) AS trip
FROM VehicleShift s JOIN FleetVehicle v ON v.vehicle_id = s.vehicle_id
WHERE s.shift_date >= '2024-08-05' AND s.shift_date < '2024-08-12'
GROUP BY v.vehicle_type, s.vehicle_id
) per_vehicle
GROUP BY vehicle_type
ORDER BY utilisation_pct IS NULL, utilisation_pct DESC, vehicle_type← Monthly Completed Trips, GMV and Average Fare · Promo Campaign Redemptions and Discount Burn →