Ola: Auto, Mini and Prime Ride Mix in Each City

Medium
840.

Ola: Auto, Mini and Prime Ride Mix in Each City

City managers want one line per city showing how completed rides split between the three product families: auto, mini and prime (prime_sedan and prime_suv together).

For every city with at least one completed ride, return city, auto_rides, mini_rides, prime_rides and top_category — the family with the most completed rides, written auto, mini or prime. If families tie for the most, take the cheaper one: auto before mini before prime. Cancelled rides are ignored. Order the rows by city.

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
cityvarchar
categoryenumauto, mini, prime_sedan, prime_suv
statusenumcompleted, cancelled
ride_datedate

Primary key: ride_id.

One row per ride request.

Example

The rows must come back in the order shown.

CabRide

ride_idcitycategorystatusride_date
1Bengaluruautocompleted2024-10-01
2Bengaluruprime_sedancompleted2024-10-01
3Bengaluruprime_suvcompleted2024-10-02
4Bengaluruminicancelled2024-10-02
5Puneminicompleted2024-10-01
6Puneautocompleted2024-10-01
7Puneautocancelled2024-10-03
8Chennaiprime_suvcancelled2024-10-02
9Delhiminicompleted2024-10-03

Output

cityauto_ridesmini_ridesprime_ridestop_category
Bengaluru102prime
Delhi010mini
Pune110auto
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: Auto, Mini and Prime Ride Mix in Each City

City managers want one line per city showing how completed rides split between the three product families: auto, mini and prime (prime_sedan and prime_suv…

  • Difficulty: Medium
  • Topics: Conditional Logic, Aggregation, Subqueries
  • Dialect: MySQL
  • Problem: #840

Problem statement

City managers want one line per city showing how completed rides split between the three product families: auto, mini and prime (prime_sedan and prime_suv together).

For every city with at least one completed ride, return city, auto_rides, mini_rides, prime_rides and top_category — the family with the most completed rides, written auto, mini or prime. If families tie for the most, take the cheaper one: auto before mini before prime. Cancelled rides are ignored. Order the rows by city.

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
cityvarchar
categoryenum(auto, mini, prime_sedan, prime_suv)
statusenum(completed, cancelled)
ride_datedate

Primary key: ride_id.

One row per ride request.

Examples

Example 1

CabRide

ride_idcitycategorystatusride_date
1Bengaluruautocompleted2024-10-01
2Bengaluruprime_sedancompleted2024-10-01
3Bengaluruprime_suvcompleted2024-10-02
4Bengaluruminicancelled2024-10-02
5Puneminicompleted2024-10-01
6Puneautocompleted2024-10-01
7Puneautocancelled2024-10-03
8Chennaiprime_suvcancelled2024-10-02
9Delhiminicompleted2024-10-03

Output

cityauto_ridesmini_ridesprime_ridestop_category
Bengaluru102prime
Delhi010mini
Pune110auto

How to solve Ola: Auto, Mini and Prime Ride Mix in Each City

The output has one row per city and one column per family, so it is a pivot done with conditional aggregation: after keeping completed rides only, each SUM(CASE WHEN … THEN 1 ELSE 0 END) counts one family, and category IN ('prime_sedan', 'prime_suv') folds the two prime categories into one column. Families a city never sold show 0 rather than NULL because the ELSE 0 contributes for every row.

The top family is a comparison of three numbers in the same row. Testing in price order with >= resolves ties exactly as asked: auto wins if it is at least as large as both others; otherwise mini wins if it is at least as large as prime; otherwise prime. Because auto was already ruled out in the second branch, mini only needs to be compared with prime.

The alternative keeps the data long instead: one row per city and family, ranked with ROW_NUMBER() by rides and then by a price rank, and the three columns looked up with scalar subqueries. It generalises better to many categories, while the pivot is a single pass.

Reference solution (MySQL)

WITH c AS (
  SELECT city,
         SUM(CASE WHEN category = 'auto' THEN 1 ELSE 0 END) AS auto_rides,
         SUM(CASE WHEN category = 'mini' THEN 1 ELSE 0 END) AS mini_rides,
         SUM(CASE WHEN category IN ('prime_sedan', 'prime_suv') THEN 1 ELSE 0 END) AS prime_rides
  FROM CabRide
  WHERE status = 'completed'
  GROUP BY city
)
SELECT city, auto_rides, mini_rides, prime_rides,
       CASE WHEN auto_rides >= mini_rides AND auto_rides >= prime_rides THEN 'auto'
            WHEN mini_rides >= prime_rides THEN 'mini'
            ELSE 'prime' END AS top_category
FROM c
ORDER BY city

Another way

WITH fam AS (
  SELECT city, CASE WHEN category LIKE 'prime%' THEN 'prime' ELSE category END AS family,
         CASE category WHEN 'auto' THEN 1 WHEN 'mini' THEN 2 ELSE 3 END AS price_rank
  FROM CabRide WHERE status = 'completed'
), counts AS (
  SELECT city, family, price_rank, COUNT(*) AS rides FROM fam GROUP BY city, family, price_rank
), best AS (
  SELECT city, family, ROW_NUMBER() OVER (PARTITION BY city ORDER BY rides DESC, price_rank) AS rn FROM counts
)
SELECT b.city,
       COALESCE((SELECT rides FROM counts x WHERE x.city = b.city AND x.family = 'auto'), 0) AS auto_rides,
       COALESCE((SELECT rides FROM counts x WHERE x.city = b.city AND x.family = 'mini'), 0) AS mini_rides,
       COALESCE((SELECT rides FROM counts x WHERE x.city = b.city AND x.family = 'prime'), 0) AS prime_rides,
       b.family AS top_category
FROM best b WHERE b.rn = 1
ORDER BY b.city

← Ola: Riders Whose Main Ride Moved From Auto to Cabs · Ola: Scooters Behind Their Model's Newest Firmware →