Uber: Surge Share of Bengaluru Trips by Hour of Day
The marketplace team wants to know at which hours Bengaluru riders most often pay surge pricing, to plan driver incentives for those hours.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation, Dates
- Dialect: MySQL
- Problem: #981
Problem statement
The marketplace team wants to know at which hours Bengaluru riders most often pay surge pricing, to plan driver incentives for those hours.
Consider only completed trips in Bengaluru. For every hour of the day (0–23, from pickup_at) that has at least one such trip, return hour_of_day, trips (the number of trips) and surge_pct — the percentage of those trips whose surge_multiplier is greater than 1.0, rounded to 2 decimal places. Order the rows by hour_of_day.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: CompletedTrip
| Column | Type |
|---|---|
| trip_id | int |
| city | varchar |
| pickup_at | datetime |
| surge_multiplier | decimal |
Primary key: trip_id.
One row per completed trip. A surge_multiplier of 1.0 means the normal fare; 1.5 means 50% above it.
Examples
Example 1
CompletedTrip
| trip_id | city | pickup_at | surge_multiplier |
|---|---|---|---|
| 1 | Bengaluru | 2024-07-01 08:05:00 | 1 |
| 2 | Bengaluru | 2024-07-01 08:40:00 | 1.5 |
| 3 | Bengaluru | 2024-07-02 08:55:00 | 1.2 |
| 4 | Bengaluru | 2024-07-02 09:10:00 | 1 |
| 5 | Bengaluru | 2024-07-01 18:20:00 | 2 |
| 6 | Bengaluru | 2024-07-01 18:45:00 | 1.8 |
| 7 | Bengaluru | 2024-07-02 18:59:00 | 1 |
| 8 | Bengaluru | 2024-07-03 18:30:00 | 1 |
| 9 | Mumbai | 2024-07-01 08:10:00 | 2 |
Output
| hour_of_day | trips | surge_pct |
|---|---|---|
| 8 | 3 | 66.67 |
| 9 | 1 | 0 |
| 18 | 4 | 50 |
How to solve Uber: Surge Share of Bengaluru Trips by Hour of Day
The grouping key is not a stored column but a value computed from one: HOUR(pickup_at) folds every day's trips at, say, 08:xx into the same bucket. After filtering to Bengaluru, GROUP BY HOUR(pickup_at) forms one group per hour that has trips, and COUNT(*) is the trips column.
The share is a conditional aggregate: SUM(CASE WHEN surge_multiplier > 1 THEN 1 ELSE 0 END) counts the surged trips in the group; multiplying by 100 before dividing by COUNT(*) gives a percentage, and ROUND(…, 2) the requested precision. The comparison is strict, so a trip at exactly 1.0 is normal fare. Multiply before dividing, and with / (decimal division) — integer division would truncate a third to zero.
The alternative computes the hour in a derived table and averages a flag that is 100 for a surged trip and 0 otherwise: the mean of that flag is the same percentage. Hours with no Bengaluru trips simply have no group, which is what the statement asks. Both are one scan and one aggregation.
Reference solution (MySQL)
SELECT HOUR(pickup_at) AS hour_of_day,
COUNT(*) AS trips,
ROUND(100 * SUM(CASE WHEN surge_multiplier > 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS surge_pct
FROM CompletedTrip
WHERE city = 'Bengaluru'
GROUP BY HOUR(pickup_at)
ORDER BY hour_of_dayAnother way
SELECT h AS hour_of_day, COUNT(*) AS trips, ROUND(AVG(IF(surge_multiplier > 1, 100, 0)), 2) AS surge_pct
FROM (SELECT HOUR(pickup_at) AS h, surge_multiplier FROM CompletedTrip WHERE city = 'Bengaluru') t
GROUP BY h
ORDER BY h← Uber: Cities With Two or More Critical Safety Reports · Uber: Uber Eats Orders Handed to Another Courier →