Ola: Riders Whose Main Ride Moved From Auto to Cabs

Medium
839.

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.

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

ColumnTypeValues
ride_idint
rider_idint
categoryenumauto, mini, prime_sedan, prime_suv
statusenumcompleted, cancelled
requested_atdatetime

Primary key: ride_id.

One row per ride request.

Example

The rows must come back in the order shown.

CabRide

ride_idrider_idcategorystatusrequested_at
171autocompleted2024-09-03 09:00:00
271autocompleted2024-09-10 09:00:00
371minicompleted2024-09-18 19:00:00
471prime_sedancompleted2024-10-02 09:00:00
571prime_suvcompleted2024-10-09 09:00:00
671autocompleted2024-10-15 09:00:00
772autocompleted2024-09-05 08:00:00
872minicompleted2024-09-06 08:00:00
972minicompleted2024-10-01 08:00:00
1073autocompleted2024-09-30 23:30:00
6 more rows

Output

rider_idsept_ridesoct_ridesoct_family
7133prime
7221mini
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: 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

ColumnType
ride_idint
rider_idint
categoryenum(auto, mini, prime_sedan, prime_suv)
statusenum(completed, cancelled)
requested_atdatetime

Primary key: ride_id.

One row per ride request.

Examples

Example 1

CabRide

ride_idrider_idcategorystatusrequested_at
171autocompleted2024-09-03 09:00:00
271autocompleted2024-09-10 09:00:00
371minicompleted2024-09-18 19:00:00
471prime_sedancompleted2024-10-02 09:00:00
571prime_suvcompleted2024-10-09 09:00:00
671autocompleted2024-10-15 09:00:00
772autocompleted2024-09-05 08:00:00
872minicompleted2024-09-06 08:00:00
972minicompleted2024-10-01 08:00:00
1073autocompleted2024-09-30 23:30:00
1173minicancelled2024-10-04 10:00:00
1274minicompleted2024-09-12 12:00:00
1374autocompleted2024-10-12 12:00:00
1475autocompleted2024-09-20 18:00:00
1575autocompleted2024-10-20 18:00:00
1675minicompleted2024-10-21 18:00:00

Output

rider_idsept_ridesoct_ridesoct_family
7133prime
7221mini

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_id

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