Ola: Rentals Bill With Extra Kilometres and Minutes
An hourly rental includes a fixed number of minutes and kilometres; anything beyond is charged per extra kilometre and per extra minute.
- Difficulty: Medium
- Topics: Conditional Logic, Dates, Joins
- Dialect: MySQL
- Problem: #834
Problem statement
An hourly rental includes a fixed number of minutes and kilometres; anything beyond is charged per extra kilometre and per extra minute. The Rentals billing team recomputes each finished trip's bill to audit the invoices.
For every finished trip (ended_at is not NULL), return trip_id, extra_km, extra_minutes and total_fare. The trip's minutes are the whole minutes from started_at to ended_at (a partial minute is not charged). extra_km and extra_minutes are how far the trip went over the package's allowance, or 0 when it stayed within it. total_fare = base_fare + extra_km × per_extra_km + extra_minutes × per_extra_min. Order the rows by trip_id.
A practice scenario set at Ola: the tables and data are invented, and this is not a question from, or endorsed by, Ola.
Tables
Table: RentalPackage
| Column | Type |
|---|---|
| package_id | int |
| package_name | varchar |
| included_minutes | int |
| included_km | int |
| base_fare | int |
| per_extra_km | int |
| per_extra_min | int |
Primary key: package_id.
One row per package; all money in rupees.
Table: RentalTrip
| Column | Type |
|---|---|
| trip_id | int |
| package_id | int |
| started_at | datetime |
| ended_at | datetime |
| km_driven | int |
Primary key: trip_id.
ended_at is NULL while the trip is still running.
Examples
Example 1
RentalPackage
| package_id | package_name | included_minutes | included_km | base_fare | per_extra_km | per_extra_min |
|---|---|---|---|---|---|---|
| 1 | 1 hr 10 km | 60 | 10 | 249 | 14 | 2 |
| 2 | 4 hr 40 km | 240 | 40 | 849 | 13 | 2 |
RentalTrip
| trip_id | package_id | started_at | ended_at | km_driven |
|---|---|---|---|---|
| 101 | 1 | 2024-08-05 10:00:00 | 2024-08-05 10:59:59 | 10 |
| 102 | 1 | 2024-08-05 11:00:00 | 2024-08-05 12:15:30 | 14 |
| 103 | 2 | 2024-08-05 09:00:00 | 2024-08-05 12:30:00 | 52 |
| 104 | 2 | 2024-08-05 13:00:00 | NULL | 20 |
| 105 | 2 | 2024-08-05 22:00:00 | 2024-08-06 02:10:00 | 38 |
Output
| trip_id | extra_km | extra_minutes | total_fare |
|---|---|---|---|
| 101 | 0 | 0 | 249 |
| 102 | 4 | 15 | 335 |
| 103 | 12 | 0 | 1005 |
| 105 | 0 | 10 | 869 |
How to solve Ola: Rentals Bill With Extra Kilometres and Minutes
Each trip is billed from its own row plus its package's row, so the query joins RentalTrip to RentalPackage and drops trips still running (ended_at IS NULL).
The overage on each meter is "used minus included, but never below zero". GREATEST(x, 0) expresses that floor directly. For time, TIMESTAMPDIFF(MINUTE, started_at, ended_at) returns whole minutes and truncates the rest, so a trip of 59 minutes 59 seconds is 59 minutes, and one that ends exactly at the allowance is 0 minutes over. Trips that cross midnight are fine because the difference is taken on full timestamps. Computing extra_km and extra_minutes once in a CTE lets the outer query build total_fare from the named values, which keeps the formula readable and the three columns consistent.
The alternative writes the floor as CASE WHEN used > included THEN used - included ELSE 0 END and derives whole minutes from seconds with DIV 60, repeating the expressions inline. Both are a single pass over the trips with a primary-key lookup of the package.
Reference solution (MySQL)
WITH t AS (
SELECT r.trip_id, p.base_fare, p.per_extra_km, p.per_extra_min,
GREATEST(r.km_driven - p.included_km, 0) AS extra_km,
GREATEST(TIMESTAMPDIFF(MINUTE, r.started_at, r.ended_at) - p.included_minutes, 0) AS extra_minutes
FROM RentalTrip r
JOIN RentalPackage p ON p.package_id = r.package_id
WHERE r.ended_at IS NOT NULL
)
SELECT trip_id, extra_km, extra_minutes,
base_fare + extra_km * per_extra_km + extra_minutes * per_extra_min AS total_fare
FROM t
ORDER BY trip_idAnother way
SELECT r.trip_id,
CASE WHEN r.km_driven > p.included_km THEN r.km_driven - p.included_km ELSE 0 END AS extra_km,
CASE WHEN TIMESTAMPDIFF(SECOND, r.started_at, r.ended_at) DIV 60 > p.included_minutes
THEN TIMESTAMPDIFF(SECOND, r.started_at, r.ended_at) DIV 60 - p.included_minutes ELSE 0 END AS extra_minutes,
p.base_fare
+ (CASE WHEN r.km_driven > p.included_km THEN r.km_driven - p.included_km ELSE 0 END) * p.per_extra_km
+ (CASE WHEN TIMESTAMPDIFF(SECOND, r.started_at, r.ended_at) DIV 60 > p.included_minutes
THEN TIMESTAMPDIFF(SECOND, r.started_at, r.ended_at) DIV 60 - p.included_minutes ELSE 0 END) * p.per_extra_min AS total_fare
FROM RentalTrip r JOIN RentalPackage p ON p.package_id = r.package_id
WHERE r.ended_at IS NOT NULL
ORDER BY r.trip_id← Ola: Test Riders Who Bought Within 30 Days, per Centre · Ola: New Riders Who Took a Second Ride Within 7 Days →