Ola: Riders Whose Main Ride Moved From Auto to Cabs
The marketing team ran an October campaign to move auto riders into cabs, and wants the riders whose habit actually changed.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #839
Problem statement
The marketing team ran an October campaign to move auto riders into cabs, and wants the riders whose habit actually changed.
Group categories into three families: auto, mini, and prime (prime_sedan and prime_suv). A rider's main family in a month is the family with the most completed rides that month; on a tie, the cheaper family wins (auto, then mini, then prime). Return every rider whose main family was auto in September 2024 and not auto in October 2024 (a rider with no completed ride in October has no main family and is not returned). Return rider_id, sept_rides and oct_rides (all completed rides in each month) and oct_family, ordered 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: CabRide
| Column | Type |
|---|---|
| ride_id | int |
| rider_id | int |
| category | enum(auto, mini, prime_sedan, prime_suv) |
| status | enum(completed, cancelled) |
| requested_at | datetime |
Primary key: ride_id.
One row per ride request.
Examples
Example 1
CabRide
| ride_id | rider_id | category | status | requested_at |
|---|---|---|---|---|
| 1 | 71 | auto | completed | 2024-09-03 09:00:00 |
| 2 | 71 | auto | completed | 2024-09-10 09:00:00 |
| 3 | 71 | mini | completed | 2024-09-18 19:00:00 |
| 4 | 71 | prime_sedan | completed | 2024-10-02 09:00:00 |
| 5 | 71 | prime_suv | completed | 2024-10-09 09:00:00 |
| 6 | 71 | auto | completed | 2024-10-15 09:00:00 |
| 7 | 72 | auto | completed | 2024-09-05 08:00:00 |
| 8 | 72 | mini | completed | 2024-09-06 08:00:00 |
| 9 | 72 | mini | completed | 2024-10-01 08:00:00 |
| 10 | 73 | auto | completed | 2024-09-30 23:30:00 |
| 11 | 73 | mini | cancelled | 2024-10-04 10:00:00 |
| 12 | 74 | mini | completed | 2024-09-12 12:00:00 |
| 13 | 74 | auto | completed | 2024-10-12 12:00:00 |
| 14 | 75 | auto | completed | 2024-09-20 18:00:00 |
| 15 | 75 | auto | completed | 2024-10-20 18:00:00 |
| 16 | 75 | mini | completed | 2024-10-21 18:00:00 |
Output
| rider_id | sept_rides | oct_rides | oct_family |
|---|---|---|---|
| 71 | 3 | 3 | prime |
| 72 | 2 | 1 | mini |
How to solve Ola: Riders Whose Main Ride Moved From Auto to Cabs
The question compares a statistical mode — each rider's most-used family — across two months. First the raw categories are mapped to families (prime_sedan and prime_suv become prime), cancelled rides and rides outside September–October are dropped, and the rows are counted per rider, month and family.
Inside each rider-month, ROW_NUMBER() ordered by the count descending and then by price rank (auto 1, mini 2, prime 3) puts the main family first, so a 1–1 tie between auto and mini goes to auto, as the statement requires. In the same pass SUM(n) OVER (PARTITION BY rider_id, mon) gives the month's total completed rides. Joining the September winner to the October winner with an inner join keeps only riders who completed rides in both months; the filters then demand auto in September and something else in October. A rider whose only October ride was cancelled has no October row and drops out.
The alternative finds the mode without window functions: a family wins when no other family of the same rider-month has more rides, or as many and an earlier name — the names happen to sort in price order. It totals the month with a correlated SUM. Both scan the rides once and then work on the much smaller per-family counts.
Reference solution (MySQL)
WITH rides AS (
SELECT rider_id, DATE_FORMAT(requested_at, '%Y-%m') AS mon,
CASE WHEN category IN ('prime_sedan', 'prime_suv') THEN 'prime' ELSE category END AS family
FROM CabRide
WHERE status = 'completed' AND requested_at >= '2024-09-01' AND requested_at < '2024-11-01'
), per_family AS (
SELECT rider_id, mon, family, COUNT(*) AS n
FROM rides
GROUP BY rider_id, mon, family
), ranked AS (
SELECT rider_id, mon, family,
SUM(n) OVER (PARTITION BY rider_id, mon) AS month_rides,
ROW_NUMBER() OVER (PARTITION BY rider_id, mon
ORDER BY n DESC, CASE family WHEN 'auto' THEN 1 WHEN 'mini' THEN 2 ELSE 3 END) AS rn
FROM per_family
)
SELECT s.rider_id, s.month_rides AS sept_rides, o.month_rides AS oct_rides, o.family AS oct_family
FROM ranked s
JOIN ranked o ON o.rider_id = s.rider_id AND o.mon = '2024-10' AND o.rn = 1
WHERE s.mon = '2024-09' AND s.rn = 1 AND s.family = 'auto' AND o.family <> 'auto'
ORDER BY s.rider_idAnother way
WITH per_family AS (
SELECT rider_id, YEAR(requested_at) * 100 + MONTH(requested_at) AS mon,
IF(category LIKE 'prime%', 'prime', category) AS family, COUNT(*) AS n
FROM CabRide
WHERE status = 'completed' AND YEAR(requested_at) = 2024 AND MONTH(requested_at) IN (9, 10)
GROUP BY rider_id, YEAR(requested_at) * 100 + MONTH(requested_at), IF(category LIKE 'prime%', 'prime', category)
), main AS (
SELECT a.rider_id, a.mon, a.family,
(SELECT SUM(t.n) FROM per_family t WHERE t.rider_id = a.rider_id AND t.mon = a.mon) AS month_rides
FROM per_family a
WHERE NOT EXISTS (SELECT 1 FROM per_family b
WHERE b.rider_id = a.rider_id AND b.mon = a.mon
AND (b.n > a.n OR (b.n = a.n AND b.family < a.family)))
)
SELECT s.rider_id, s.month_rides AS sept_rides, o.month_rides AS oct_rides, o.family AS oct_family
FROM main s JOIN main o ON o.rider_id = s.rider_id AND o.mon = 202410
WHERE s.mon = 202409 AND s.family = 'auto' AND o.family IN ('mini', 'prime')
ORDER BY s.rider_id← Ola: OTA Update Adoption in Its First Week, by City · Ola: Auto, Mini and Prime Ride Mix in Each City →