Ola: Test Ride Bookings and No-Shows per Experience Centre

Easy
828.

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

ColumnType
centre_idint
centre_namevarchar
cityvarchar

Primary key: centre_id.

One row per experience centre; names are unique.

Table: TestRide

ColumnTypeValues
ride_idint
centre_idint
customer_idint
slot_atdatetime
outcomeenumcompleted, no_show, rescheduled

Primary key: ride_id.

One row per booked test-ride slot.

Example

The rows must come back in the order shown.

ExperienceCentre

centre_idcentre_namecity
1EC IndiranagarBengaluru
2EC KothrudPune
3EC PowaiMumbai
4EC Anna NagarChennai

TestRide

ride_idcentre_idcustomer_idslot_atoutcome
115012024-09-07 11:00:00completed
215022024-09-07 12:00:00no_show
315032024-09-08 10:00:00no_show
425042024-09-07 16:00:00completed
525052024-09-08 17:00:00rescheduled
645062024-09-08 18:00:00no_show
745072024-09-09 11:00:00no_show
825082024-09-09 12:00:00completed

Output

centre_namebookedcompletedno_shows
EC Anna Nagar202
EC Indiranagar312
EC Kothrud320
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: 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

ColumnType
centre_idint
centre_namevarchar
cityvarchar

Primary key: centre_id.

One row per experience centre; names are unique.

Table: TestRide

ColumnType
ride_idint
centre_idint
customer_idint
slot_atdatetime
outcomeenum(completed, no_show, rescheduled)

Primary key: ride_id.

One row per booked test-ride slot.

Examples

Example 1

ExperienceCentre

centre_idcentre_namecity
1EC IndiranagarBengaluru
2EC KothrudPune
3EC PowaiMumbai
4EC Anna NagarChennai

TestRide

ride_idcentre_idcustomer_idslot_atoutcome
115012024-09-07 11:00:00completed
215022024-09-07 12:00:00no_show
315032024-09-08 10:00:00no_show
425042024-09-07 16:00:00completed
525052024-09-08 17:00:00rescheduled
645062024-09-08 18:00:00no_show
745072024-09-09 11:00:00no_show
825082024-09-09 12:00:00completed

Output

centre_namebookedcompletedno_shows
EC Anna Nagar202
EC Indiranagar312
EC Kothrud320

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_name

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

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