Ola: New Riders Who Took a Second Ride Within 7 Days

Medium
835.

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.

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

ColumnTypeValues
ride_idint
rider_idint
requested_atdatetime
statusenumcompleted, cancelled

Primary key: ride_id.

One row per ride request. A rider never has two requests at the same second.

Example

The rows must come back in the order shown.

Ride

ride_idrider_idrequested_atstatus
1512024-05-30 09:00:00completed
2512024-06-02 10:00:00completed
3522024-06-03 08:30:00completed
4522024-06-10 22:15:00completed
5532024-06-05 07:00:00cancelled
6532024-06-06 07:10:00completed
7532024-06-14 18:00:00completed
8542024-06-30 23:50:00completed
9552024-06-12 12:00:00completed
10552024-06-12 19:00:00completed

Output

rider_idfirst_ride_onsecond_ride_onactivated
522024-06-032024-06-101
532024-06-062024-06-140
542024-06-30NULL0
552024-06-122024-06-121
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: 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

ColumnType
ride_idint
rider_idint
requested_atdatetime
statusenum(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_idrider_idrequested_atstatus
1512024-05-30 09:00:00completed
2512024-06-02 10:00:00completed
3522024-06-03 08:30:00completed
4522024-06-10 22:15:00completed
5532024-06-05 07:00:00cancelled
6532024-06-06 07:10:00completed
7532024-06-14 18:00:00completed
8542024-06-30 23:50:00completed
9552024-06-12 12:00:00completed
10552024-06-12 19:00:00completed

Output

rider_idfirst_ride_onsecond_ride_onactivated
522024-06-032024-06-101
532024-06-062024-06-140
542024-06-30NULL0
552024-06-122024-06-121

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_id

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

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