Fleet Utilisation by Vehicle Type for One Week

Medium
312.

Fleet Utilisation by Vehicle Type for One Week

MediumJoinsAggregation

MySQL

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

ColumnTypeValues
vehicle_idint
registration_novarchar
vehicle_typeenumauto, bike, cab, ev_cab

Primary key: vehicle_id.

Table: VehicleShift

ColumnType
shift_idint
vehicle_idint
shift_datedate
online_minutesint
on_trip_minutesint

Primary key: shift_id.

One row per vehicle per shift; on_trip_minutes never exceeds online_minutes.

Example

The rows must come back in the order shown.

FleetVehicle

vehicle_idregistration_novehicle_type
1KA01AB1001cab
2KA01AB1002cab
3KA05MN2001bike
4KA03XY3001auto
5KA51EV4001ev_cab

VehicleShift

shift_idvehicle_idshift_dateonline_minuteson_trip_minutes
112024-08-05600420
212024-08-06480300
322024-08-11540270
432024-08-07300240
542024-08-04600500
642024-08-12600450
752024-08-0800
832024-08-0912060

Output

vehicle_typevehicles_onlineutilisation_pct
bike171.43
cab261.11
ev_cab1NULL
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
vehicle_idint
registration_novarchar
vehicle_typeenum(auto, bike, cab, ev_cab)

Primary key: vehicle_id.

Table: VehicleShift

ColumnType
shift_idint
vehicle_idint
shift_datedate
online_minutesint
on_trip_minutesint

Primary key: shift_id.

One row per vehicle per shift; on_trip_minutes never exceeds online_minutes.

Examples

Example 1

FleetVehicle

vehicle_idregistration_novehicle_type
1KA01AB1001cab
2KA01AB1002cab
3KA05MN2001bike
4KA03XY3001auto
5KA51EV4001ev_cab

VehicleShift

shift_idvehicle_idshift_dateonline_minuteson_trip_minutes
112024-08-05600420
212024-08-06480300
322024-08-11540270
432024-08-07300240
542024-08-04600500
642024-08-12600450
752024-08-0800
832024-08-0912060

Output

vehicle_typevehicles_onlineutilisation_pct
bike171.43
cab261.11
ev_cab1NULL

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_type

Another 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 →