Ola: Completed Rentals Bookings per Hourly Package

Easy
825.

Ola: Completed Rentals Bookings per Hourly Package

The Rentals product manager at Ola wants to see how each hourly package is doing, including packages that have not sold at all, before trimming the menu.

For every package, return package_name, hours and completed_bookings — the number of its bookings whose status is completed (0 when there are none; cancelled bookings and bookings that found no driver do not count). Order the rows by hours, then by package_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
hoursint
included_kmint
base_fareint

Primary key: package_id.

One row per hourly package on the menu. Two packages can have the same number of hours with different kilometre allowances.

Table: RentalBooking

ColumnTypeValues
booking_idint
package_idint
rider_idint
booked_ondate
statusenumcompleted, cancelled, no_driver

Primary key: booking_id.

One row per rental booking; package_id always names a package.

Example

The rows must come back in the order shown.

RentalPackage

package_idpackage_namehoursincluded_kmbase_fare
34 hr 40 km440849
11 hr 10 km110249
58 hr 80 km8801649
24 hr 50 km450949
42 hr 20 km220449

RentalBooking

booking_idpackage_idrider_idbooked_onstatus
5013712024-08-01completed
5023722024-08-01completed
5031732024-08-02cancelled
5042742024-08-02completed
5054752024-08-03no_driver
5064712024-08-04completed
5073762024-08-04cancelled

Output

package_namehourscompleted_bookings
1 hr 10 km10
2 hr 20 km21
4 hr 50 km41
4 hr 40 km42
8 hr 80 km80
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: Completed Rentals Bookings per Hourly Package

The Rentals product manager at Ola wants to see how each hourly package is doing, including packages that have not sold at all, before trimming the menu.

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #825

Problem statement

The Rentals product manager at Ola wants to see how each hourly package is doing, including packages that have not sold at all, before trimming the menu.

For every package, return package_name, hours and completed_bookings — the number of its bookings whose status is completed (0 when there are none; cancelled bookings and bookings that found no driver do not count). Order the rows by hours, then by package_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
hoursint
included_kmint
base_fareint

Primary key: package_id.

One row per hourly package on the menu. Two packages can have the same number of hours with different kilometre allowances.

Table: RentalBooking

ColumnType
booking_idint
package_idint
rider_idint
booked_ondate
statusenum(completed, cancelled, no_driver)

Primary key: booking_id.

One row per rental booking; package_id always names a package.

Examples

Example 1

RentalPackage

package_idpackage_namehoursincluded_kmbase_fare
34 hr 40 km440849
11 hr 10 km110249
58 hr 80 km8801649
24 hr 50 km450949
42 hr 20 km220449

RentalBooking

booking_idpackage_idrider_idbooked_onstatus
5013712024-08-01completed
5023722024-08-01completed
5031732024-08-02cancelled
5042742024-08-02completed
5054752024-08-03no_driver
5064712024-08-04completed
5073762024-08-04cancelled

Output

package_namehourscompleted_bookings
1 hr 10 km10
2 hr 20 km21
4 hr 50 km41
4 hr 40 km42
8 hr 80 km80

How to solve Ola: Completed Rentals Bookings per Hourly Package

The menu has to be listed in full, so the query starts from RentalPackage and attaches bookings with a LEFT JOIN. The trap is where the status condition goes. In WHERE, it runs after the join and throws away the NULL-extended row of a package with no completed bookings, so that package vanishes. In the join's ON clause it only decides which bookings attach, and a package with none keeps one row whose booking columns are NULL.

COUNT(b.booking_id) then counts only real bookings, so the unmatched package shows 0. COUNT(*) would count the NULL row and show 1. Grouping by package_id keeps two packages apart even if they share hours, and the tie on hours is broken by package_id as asked.

The alternatives count with a correlated subquery per package, or join every booking and add up a CASE that is 1 only for completed ones (wrapped in COALESCE, since the sum over a lone NULL row is NULL). All of them read each booking once.

Reference solution (MySQL)

SELECT p.package_name, p.hours, COUNT(b.booking_id) AS completed_bookings
FROM RentalPackage p
LEFT JOIN RentalBooking b ON b.package_id = p.package_id AND b.status = 'completed'
GROUP BY p.package_id, p.package_name, p.hours
ORDER BY p.hours, p.package_id

Another way

SELECT p.package_name, p.hours, (SELECT COUNT(*) FROM RentalBooking b WHERE b.package_id = p.package_id AND b.status = 'completed') AS completed_bookings FROM RentalPackage p ORDER BY p.hours, p.package_id

Another way

SELECT p.package_name, p.hours, COALESCE(SUM(CASE WHEN b.status = 'completed' THEN 1 ELSE 0 END), 0) AS completed_bookings FROM RentalPackage p LEFT JOIN RentalBooking b ON b.package_id = p.package_id GROUP BY p.package_id, p.package_name, p.hours ORDER BY p.hours, p.package_id

← Ola: Hypercharger Stations Idle Through a Whole Week · Ola: Wallets Topped Up Over ₹10,000 in May 2024 →