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
| Column | Type |
|---|---|
| package_id | int |
| package_name | varchar |
| hours | int |
| included_km | int |
| base_fare | int |
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
| Column | Type |
|---|---|
| booking_id | int |
| package_id | int |
| rider_id | int |
| booked_on | date |
| status | enum(completed, cancelled, no_driver) |
Primary key: booking_id.
One row per rental booking; package_id always names a package.
Examples
Example 1
RentalPackage
| package_id | package_name | hours | included_km | base_fare |
|---|---|---|---|---|
| 3 | 4 hr 40 km | 4 | 40 | 849 |
| 1 | 1 hr 10 km | 1 | 10 | 249 |
| 5 | 8 hr 80 km | 8 | 80 | 1649 |
| 2 | 4 hr 50 km | 4 | 50 | 949 |
| 4 | 2 hr 20 km | 2 | 20 | 449 |
RentalBooking
| booking_id | package_id | rider_id | booked_on | status |
|---|---|---|---|---|
| 501 | 3 | 71 | 2024-08-01 | completed |
| 502 | 3 | 72 | 2024-08-01 | completed |
| 503 | 1 | 73 | 2024-08-02 | cancelled |
| 504 | 2 | 74 | 2024-08-02 | completed |
| 505 | 4 | 75 | 2024-08-03 | no_driver |
| 506 | 4 | 71 | 2024-08-04 | completed |
| 507 | 3 | 76 | 2024-08-04 | cancelled |
Output
| package_name | hours | completed_bookings |
|---|---|---|
| 1 hr 10 km | 1 | 0 |
| 2 hr 20 km | 2 | 1 |
| 4 hr 50 km | 4 | 1 |
| 4 hr 40 km | 4 | 2 |
| 8 hr 80 km | 8 | 0 |
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_idAnother 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_idAnother 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 →