Uber: Surge Share of Bengaluru Trips by Hour of Day

Medium
981.

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.

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

ColumnType
trip_idint
cityvarchar
pickup_atdatetime
surge_multiplierdecimal

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.

Example

The rows must come back in the order shown.

CompletedTrip

trip_idcitypickup_atsurge_multiplier
1Bengaluru2024-07-01 08:05:001
2Bengaluru2024-07-01 08:40:001.5
3Bengaluru2024-07-02 08:55:001.2
4Bengaluru2024-07-02 09:10:001
5Bengaluru2024-07-01 18:20:002
6Bengaluru2024-07-01 18:45:001.8
7Bengaluru2024-07-02 18:59:001
8Bengaluru2024-07-03 18:30:001
9Mumbai2024-07-01 08:10:002

Output

hour_of_daytripssurge_pct
8366.67
910
18450
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.

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

ColumnType
trip_idint
cityvarchar
pickup_atdatetime
surge_multiplierdecimal

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_idcitypickup_atsurge_multiplier
1Bengaluru2024-07-01 08:05:001
2Bengaluru2024-07-01 08:40:001.5
3Bengaluru2024-07-02 08:55:001.2
4Bengaluru2024-07-02 09:10:001
5Bengaluru2024-07-01 18:20:002
6Bengaluru2024-07-01 18:45:001.8
7Bengaluru2024-07-02 18:59:001
8Bengaluru2024-07-03 18:30:001
9Mumbai2024-07-01 08:10:002

Output

hour_of_daytripssurge_pct
8366.67
910
18450

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_day

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