Ola: Test Riders Who Bought Within 30 Days, per Centre
The retail team at Ola measures how well each experience centre turns test rides into scooter orders.
- Difficulty: Medium
- Topics: Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #833
Problem statement
The retail team at Ola measures how well each experience centre turns test rides into scooter orders.
For every centre with at least one completed test ride, return centre_name, test_riders — the number of different customers who completed a test ride there — and converted, how many of those customers placed a confirmed order from the day of such a test ride up to 30 days after it (both ends included). A customer counts once per centre however many rides or orders they have; a cancelled order or one outside the window does not convert. Order the rows by converted 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 |
Primary key: centre_id.
One row per centre; names are unique.
Table: TestRide
| Column | Type |
|---|---|
| ride_id | int |
| centre_id | int |
| customer_id | int |
| ride_date | date |
| outcome | enum(completed, no_show) |
Primary key: ride_id.
One row per booked test ride.
Table: ScooterOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| ordered_on | date |
| status | enum(confirmed, cancelled) |
Primary key: order_id.
One row per scooter order, wherever it was placed.
Examples
Example 1
ExperienceCentre
| centre_id | centre_name |
|---|---|
| 1 | EC Indiranagar |
| 2 | EC Kothrud |
| 3 | EC Powai |
TestRide
| ride_id | centre_id | customer_id | ride_date | outcome |
|---|---|---|---|---|
| 1 | 1 | 501 | 2024-05-01 | completed |
| 2 | 1 | 502 | 2024-05-03 | completed |
| 3 | 1 | 501 | 2024-05-10 | completed |
| 4 | 2 | 503 | 2024-05-04 | completed |
| 5 | 2 | 504 | 2024-05-04 | no_show |
| 6 | 3 | 505 | 2024-05-06 | no_show |
| 7 | 2 | 506 | 2024-05-20 | completed |
ScooterOrder
| order_id | customer_id | ordered_on | status |
|---|---|---|---|
| 11 | 501 | 2024-05-31 | confirmed |
| 12 | 502 | 2024-06-03 | confirmed |
| 13 | 503 | 2024-05-04 | confirmed |
| 14 | 504 | 2024-05-05 | confirmed |
| 15 | 506 | 2024-05-25 | cancelled |
Output
| centre_name | test_riders | converted |
|---|---|---|
| EC Indiranagar | 2 | 1 |
| EC Kothrud | 2 | 1 |
How to solve Ola: Test Riders Who Bought Within 30 Days, per Centre
Two counts come from the same set of completed test rides per centre, so the query groups by centre after joining. test_riders is COUNT(DISTINCT t.customer_id): a customer who rode twice at one centre is one test rider.
For converted, every completed ride is matched to the orders that would convert it — same customer, status confirmed, ordered_on between the ride date and 30 days later — with a LEFT JOIN so rides without such an order stay in the group. The matched rows carry the customer id in o.customer_id, unmatched ones carry NULL, so COUNT(DISTINCT o.customer_id) counts converted customers once each and ignores the NULLs. Putting the rule in ON rather than WHERE is what keeps unconverted riders in test_riders.
The window is inclusive at both ends: an order on the ride day counts, and so does one exactly 30 days later; an order placed before the ride does not. The alternative tests each ride with EXISTS and counts the customers for which it holds. A no-show ride never qualifies a customer, and a centre with only no-shows has no group at all.
Reference solution (MySQL)
SELECT c.centre_name,
COUNT(DISTINCT t.customer_id) AS test_riders,
COUNT(DISTINCT o.customer_id) AS converted
FROM ExperienceCentre c
JOIN TestRide t ON t.centre_id = c.centre_id AND t.outcome = 'completed'
LEFT JOIN ScooterOrder o ON o.customer_id = t.customer_id
AND o.status = 'confirmed'
AND o.ordered_on BETWEEN t.ride_date AND DATE_ADD(t.ride_date, INTERVAL 30 DAY)
GROUP BY c.centre_id, c.centre_name
ORDER BY converted DESC, c.centre_nameAnother way
SELECT c.centre_name, COUNT(DISTINCT t.customer_id) AS test_riders,
COUNT(DISTINCT CASE WHEN EXISTS (SELECT 1 FROM ScooterOrder o WHERE o.customer_id = t.customer_id AND o.status = 'confirmed'
AND DATEDIFF(o.ordered_on, t.ride_date) BETWEEN 0 AND 30) THEN t.customer_id END) AS converted
FROM ExperienceCentre c JOIN TestRide t ON t.centre_id = c.centre_id
WHERE t.outcome = 'completed'
GROUP BY c.centre_id, c.centre_name
ORDER BY converted DESC, c.centre_name← Ola: Busiest Hour of the Day at Each Hypercharger · Ola: Rentals Bill With Extra Kilometres and Minutes →