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
| Column | Type |
|---|---|
| ride_id | int |
| city | varchar |
| category | enum(auto, mini, prime_sedan, prime_suv) |
| status | enum(completed, cancelled) |
| ride_date | date |
Primary key: ride_id.
One row per ride request.
Examples
Example 1
CabRide
| ride_id | city | category | status | ride_date |
|---|---|---|---|---|
| 1 | Bengaluru | auto | completed | 2024-10-01 |
| 2 | Bengaluru | prime_sedan | completed | 2024-10-01 |
| 3 | Bengaluru | prime_suv | completed | 2024-10-02 |
| 4 | Bengaluru | mini | cancelled | 2024-10-02 |
| 5 | Pune | mini | completed | 2024-10-01 |
| 6 | Pune | auto | completed | 2024-10-01 |
| 7 | Pune | auto | cancelled | 2024-10-03 |
| 8 | Chennai | prime_suv | cancelled | 2024-10-02 |
| 9 | Delhi | mini | completed | 2024-10-03 |
Output
| city | auto_rides | mini_rides | prime_rides | top_category |
|---|---|---|---|---|
| Bengaluru | 1 | 0 | 2 | prime |
| Delhi | 0 | 1 | 0 | mini |
| Pune | 1 | 1 | 0 | auto |
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 cityAnother 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 →