Ola: Test Riders Who Bought Within 30 Days, per Centre

Medium
833.

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.

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

ColumnType
centre_idint
centre_namevarchar

Primary key: centre_id.

One row per centre; names are unique.

Table: TestRide

ColumnTypeValues
ride_idint
centre_idint
customer_idint
ride_datedate
outcomeenumcompleted, no_show

Primary key: ride_id.

One row per booked test ride.

Table: ScooterOrder

ColumnTypeValues
order_idint
customer_idint
ordered_ondate
statusenumconfirmed, cancelled

Primary key: order_id.

One row per scooter order, wherever it was placed.

Example

The rows must come back in the order shown.

ExperienceCentre

centre_idcentre_name
1EC Indiranagar
2EC Kothrud
3EC Powai

TestRide

ride_idcentre_idcustomer_idride_dateoutcome
115012024-05-01completed
215022024-05-03completed
315012024-05-10completed
425032024-05-04completed
525042024-05-04no_show
635052024-05-06no_show
725062024-05-20completed

ScooterOrder

order_idcustomer_idordered_onstatus
115012024-05-31confirmed
125022024-06-03confirmed
135032024-05-04confirmed
145042024-05-05confirmed
155062024-05-25cancelled

Output

centre_nametest_ridersconverted
EC Indiranagar21
EC Kothrud21
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 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

ColumnType
centre_idint
centre_namevarchar

Primary key: centre_id.

One row per centre; names are unique.

Table: TestRide

ColumnType
ride_idint
centre_idint
customer_idint
ride_datedate
outcomeenum(completed, no_show)

Primary key: ride_id.

One row per booked test ride.

Table: ScooterOrder

ColumnType
order_idint
customer_idint
ordered_ondate
statusenum(confirmed, cancelled)

Primary key: order_id.

One row per scooter order, wherever it was placed.

Examples

Example 1

ExperienceCentre

centre_idcentre_name
1EC Indiranagar
2EC Kothrud
3EC Powai

TestRide

ride_idcentre_idcustomer_idride_dateoutcome
115012024-05-01completed
215022024-05-03completed
315012024-05-10completed
425032024-05-04completed
525042024-05-04no_show
635052024-05-06no_show
725062024-05-20completed

ScooterOrder

order_idcustomer_idordered_onstatus
115012024-05-31confirmed
125022024-06-03confirmed
135032024-05-04confirmed
145042024-05-05confirmed
155062024-05-25cancelled

Output

centre_nametest_ridersconverted
EC Indiranagar21
EC Kothrud21

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_name

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