Ola: Rentals Bill With Extra Kilometres and Minutes

Medium
834.

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. 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

ColumnType
package_idint
package_namevarchar
included_minutesint
included_kmint
base_fareint
per_extra_kmint
per_extra_minint

Primary key: package_id.

One row per package; all money in rupees.

Table: RentalTrip

ColumnType
trip_idint
package_idint
started_atdatetime
ended_atdatetime
km_drivenint

Primary key: trip_id.

ended_at is NULL while the trip is still running.

Example

The rows must come back in the order shown.

RentalPackage

package_idpackage_nameincluded_minutesincluded_kmbase_fareper_extra_kmper_extra_min
11 hr 10 km6010249142
24 hr 40 km24040849132

RentalTrip

trip_idpackage_idstarted_atended_atkm_driven
10112024-08-05 10:00:002024-08-05 10:59:5910
10212024-08-05 11:00:002024-08-05 12:15:3014
10322024-08-05 09:00:002024-08-05 12:30:0052
10422024-08-05 13:00:00NULL20
10522024-08-05 22:00:002024-08-06 02:10:0038

Output

trip_idextra_kmextra_minutestotal_fare
10100249
102415335
1031201005
105010869
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.

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

ColumnType
package_idint
package_namevarchar
included_minutesint
included_kmint
base_fareint
per_extra_kmint
per_extra_minint

Primary key: package_id.

One row per package; all money in rupees.

Table: RentalTrip

ColumnType
trip_idint
package_idint
started_atdatetime
ended_atdatetime
km_drivenint

Primary key: trip_id.

ended_at is NULL while the trip is still running.

Examples

Example 1

RentalPackage

package_idpackage_nameincluded_minutesincluded_kmbase_fareper_extra_kmper_extra_min
11 hr 10 km6010249142
24 hr 40 km24040849132

RentalTrip

trip_idpackage_idstarted_atended_atkm_driven
10112024-08-05 10:00:002024-08-05 10:59:5910
10212024-08-05 11:00:002024-08-05 12:15:3014
10322024-08-05 09:00:002024-08-05 12:30:0052
10422024-08-05 13:00:00NULL20
10522024-08-05 22:00:002024-08-06 02:10:0038

Output

trip_idextra_kmextra_minutestotal_fare
10100249
102415335
1031201005
105010869

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_id

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