Ola: New Riders Who Took a Second Ride Within 7 Days
The growth team at Ola calls a new rider activated when their second completed ride comes soon after their first.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #835
Problem statement
The growth team at Ola calls a new rider activated when their second completed ride comes soon after their first.
Consider riders whose first completed ride ever was in June 2024. For each, return rider_id, first_ride_on and second_ride_on — the calendar dates of their first and second completed rides (NULL if there is no second yet) — and activated: 1 if the second ride's date is at most 7 days after the first ride's date (DATEDIFF ≤ 7, so the same day counts), otherwise 0. Cancelled rides are ignored throughout. Order the rows by rider_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: Ride
| Column | Type |
|---|---|
| ride_id | int |
| rider_id | int |
| requested_at | datetime |
| status | enum(completed, cancelled) |
Primary key: ride_id.
One row per ride request. A rider never has two requests at the same second.
Examples
Example 1
Ride
| ride_id | rider_id | requested_at | status |
|---|---|---|---|
| 1 | 51 | 2024-05-30 09:00:00 | completed |
| 2 | 51 | 2024-06-02 10:00:00 | completed |
| 3 | 52 | 2024-06-03 08:30:00 | completed |
| 4 | 52 | 2024-06-10 22:15:00 | completed |
| 5 | 53 | 2024-06-05 07:00:00 | cancelled |
| 6 | 53 | 2024-06-06 07:10:00 | completed |
| 7 | 53 | 2024-06-14 18:00:00 | completed |
| 8 | 54 | 2024-06-30 23:50:00 | completed |
| 9 | 55 | 2024-06-12 12:00:00 | completed |
| 10 | 55 | 2024-06-12 19:00:00 | completed |
Output
| rider_id | first_ride_on | second_ride_on | activated |
|---|---|---|---|
| 52 | 2024-06-03 | 2024-06-10 | 1 |
| 53 | 2024-06-06 | 2024-06-14 | 0 |
| 54 | 2024-06-30 | NULL | 0 |
| 55 | 2024-06-12 | 2024-06-12 | 1 |
How to solve Ola: New Riders Who Took a Second Ride Within 7 Days
The cohort is defined by a rider's first completed ride, so cancelled rides are removed first and the rest are numbered per rider with ROW_NUMBER() OVER (PARTITION BY rider_id ORDER BY requested_at). Ride 1 is the first completed ride ever; only after numbering may the query keep the riders whose ride 1 is in June. Filtering June first would make a May rider's June ride look like a first ride.
Ride 2, when it exists, is attached with a LEFT JOIN so riders with a single ride stay in the answer with a NULL second_ride_on. DATEDIFF compares calendar dates and ignores the time of day, so a second ride later the same day is 0 days and a ride on the seventh day is 7 — both activated. With no second ride, DATEDIFF is NULL, the CASE falls to ELSE, and activated is 0. CAST(… AS DATE) turns the timestamps into the dates the output asks for.
The alternatives read ride 2 with LEAD on the same window, or find the first ride with MIN and the second with a correlated MIN over later completed rides. All are one or two passes over each rider's rides.
Reference solution (MySQL)
WITH r AS (
SELECT rider_id, requested_at,
ROW_NUMBER() OVER (PARTITION BY rider_id ORDER BY requested_at) AS n
FROM Ride
WHERE status = 'completed'
), first_ride AS (
SELECT rider_id, requested_at AS first_at FROM r
WHERE n = 1 AND requested_at >= '2024-06-01' AND requested_at < '2024-07-01'
), second_ride AS (
SELECT rider_id, requested_at AS second_at FROM r WHERE n = 2
)
SELECT f.rider_id,
CAST(f.first_at AS DATE) AS first_ride_on,
CAST(s.second_at AS DATE) AS second_ride_on,
CASE WHEN DATEDIFF(s.second_at, f.first_at) <= 7 THEN 1 ELSE 0 END AS activated
FROM first_ride f
LEFT JOIN second_ride s ON s.rider_id = f.rider_id
ORDER BY f.rider_idAnother way
WITH r AS (
SELECT rider_id, requested_at,
LEAD(requested_at) OVER (PARTITION BY rider_id ORDER BY requested_at) AS next_at,
ROW_NUMBER() OVER (PARTITION BY rider_id ORDER BY requested_at) AS n
FROM Ride WHERE status = 'completed'
)
SELECT rider_id, CAST(requested_at AS DATE) AS first_ride_on, CAST(next_at AS DATE) AS second_ride_on,
IF(next_at IS NOT NULL AND DATEDIFF(next_at, requested_at) <= 7, 1, 0) AS activated
FROM r
WHERE n = 1 AND YEAR(requested_at) = 2024 AND MONTH(requested_at) = 6
ORDER BY rider_idAnother way
SELECT f.rider_id, CAST(f.first_at AS DATE) AS first_ride_on, CAST(f.second_at AS DATE) AS second_ride_on,
CASE WHEN f.second_at IS NOT NULL AND DATEDIFF(f.second_at, f.first_at) <= 7 THEN 1 ELSE 0 END AS activated
FROM (
SELECT a.rider_id, a.first_at,
(SELECT MIN(x.requested_at) FROM Ride x WHERE x.rider_id = a.rider_id AND x.status = 'completed' AND x.requested_at > a.first_at) AS second_at
FROM (SELECT rider_id, MIN(requested_at) AS first_at FROM Ride WHERE status = 'completed' GROUP BY rider_id) a
WHERE a.first_at >= '2024-06-01' AND a.first_at < '2024-07-01'
) f
ORDER BY f.rider_id← Ola: Rentals Bill With Extra Kilometres and Minutes · Ola: Wallet Cashback That Was Not Fully Credited →