Ola: Test Ride Bookings and No-Shows per Experience Centre
Experience centres let a customer book a scooter test ride, and the retail team at Ola wants to know which centres lose the most slots to people who never…
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic, Joins
- Dialect: MySQL
- Problem: #828
Problem statement
Experience centres let a customer book a scooter test ride, and the retail team at Ola wants to know which centres lose the most slots to people who never turn up.
For every centre with at least one test-ride booking, return centre_name, booked (all its bookings, whatever the outcome), completed (outcome completed) and no_shows (outcome no_show). Rescheduled bookings count only in booked. Order the rows by no_shows from highest to lowest, then by centre_name.
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: ExperienceCentre
| Column | Type |
|---|---|
| centre_id | int |
| centre_name | varchar |
| city | varchar |
Primary key: centre_id.
One row per experience centre; names are unique.
Table: TestRide
| Column | Type |
|---|---|
| ride_id | int |
| centre_id | int |
| customer_id | int |
| slot_at | datetime |
| outcome | enum(completed, no_show, rescheduled) |
Primary key: ride_id.
One row per booked test-ride slot.
Examples
Example 1
ExperienceCentre
| centre_id | centre_name | city |
|---|---|---|
| 1 | EC Indiranagar | Bengaluru |
| 2 | EC Kothrud | Pune |
| 3 | EC Powai | Mumbai |
| 4 | EC Anna Nagar | Chennai |
TestRide
| ride_id | centre_id | customer_id | slot_at | outcome |
|---|---|---|---|---|
| 1 | 1 | 501 | 2024-09-07 11:00:00 | completed |
| 2 | 1 | 502 | 2024-09-07 12:00:00 | no_show |
| 3 | 1 | 503 | 2024-09-08 10:00:00 | no_show |
| 4 | 2 | 504 | 2024-09-07 16:00:00 | completed |
| 5 | 2 | 505 | 2024-09-08 17:00:00 | rescheduled |
| 6 | 4 | 506 | 2024-09-08 18:00:00 | no_show |
| 7 | 4 | 507 | 2024-09-09 11:00:00 | no_show |
| 8 | 2 | 508 | 2024-09-09 12:00:00 | completed |
Output
| centre_name | booked | completed | no_shows |
|---|---|---|---|
| EC Anna Nagar | 2 | 0 | 2 |
| EC Indiranagar | 3 | 1 | 2 |
| EC Kothrud | 3 | 2 | 0 |
How to solve Ola: Test Ride Bookings and No-Shows per Experience Centre
All three numbers describe the same group of rows — one centre's bookings — so they come out of a single GROUP BY with conditional aggregation. COUNT(*) counts every booking. For the outcome-specific counts, a CASE turns each row into 1 when it has that outcome and 0 otherwise, and SUM adds them up; a rescheduled booking contributes 0 to both and still counts in booked.
Joining with an inner join drops centres that had no bookings, which the statement wants. Grouping by centre_id as well as the name is the safe habit even though names are unique here. The order is by no_shows descending, with the name breaking ties so the result is fixed.
In MySQL a comparison is 1 or 0, so SUM(t.outcome = 'no_show') is a shorter spelling of the same count, used in the first alternative. The second aggregates the bookings in a derived table with COUNT(IF(…, 1, NULL)) — COUNT skips the NULLs — and joins the centre names on afterwards. Each version reads the bookings once.
Reference solution (MySQL)
SELECT c.centre_name,
COUNT(*) AS booked,
SUM(CASE WHEN t.outcome = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN t.outcome = 'no_show' THEN 1 ELSE 0 END) AS no_shows
FROM ExperienceCentre c
JOIN TestRide t ON t.centre_id = c.centre_id
GROUP BY c.centre_id, c.centre_name
ORDER BY no_shows DESC, c.centre_nameAnother way
SELECT c.centre_name, COUNT(*) AS booked, SUM(t.outcome = 'completed') AS completed, SUM(t.outcome = 'no_show') AS no_shows FROM ExperienceCentre c JOIN TestRide t ON t.centre_id = c.centre_id GROUP BY c.centre_id, c.centre_name ORDER BY no_shows DESC, c.centre_nameAnother way
SELECT c.centre_name, x.booked, x.completed, x.no_shows FROM ExperienceCentre c JOIN (SELECT centre_id, COUNT(*) AS booked, COUNT(IF(outcome = 'completed', 1, NULL)) AS completed, COUNT(IF(outcome = 'no_show', 1, NULL)) AS no_shows FROM TestRide GROUP BY centre_id) x ON x.centre_id = c.centre_id ORDER BY x.no_shows DESC, c.centre_name← Ola: Driver Licences Expiring Within 30 Days · Ola: Registration Plates Cleaned for the Compliance Upload →