Airport Drop Trips Charged With Surge Pricing — SQL Problem
The pricing team of a Bengaluru cab aggregator is reviewing how often surge pricing hits riders going to the airport.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #298
Problem statement
The pricing team of a Bengaluru cab aggregator is reviewing how often surge pricing hits riders going to the airport. A trip was surged when its surge_multiplier is greater than 1.0; a multiplier of exactly 1.0 means normal fare.
Return every completed trip whose drop_zone is 'Airport' and that was surged, with the columns trip_id, driver_id and fare. Cancelled trips never appear. Order the rows by fare from highest to lowest, and by trip_id ascending when fares are equal.
Tables
Table: Trip
| Column | Type |
|---|---|
| trip_id | int |
| driver_id | int |
| pickup_zone | varchar |
| drop_zone | varchar |
| status | enum(completed, cancelled) |
| fare | int |
| surge_multiplier | decimal |
Primary key: trip_id.
One row per trip. fare is in rupees (0 for a cancelled trip); surge_multiplier is 1.0 when no surge applied.
Examples
Example 1
Trip
| trip_id | driver_id | pickup_zone | drop_zone | status | fare | surge_multiplier |
|---|---|---|---|---|---|---|
| 1 | 11 | Koramangala | Airport | completed | 1240 | 1.4 |
| 2 | 12 | Whitefield | Airport | completed | 980 | 1 |
| 3 | 13 | Hebbal | Airport | cancelled | 0 | 1.6 |
| 4 | 11 | Airport | MG Road | completed | 1100 | 1.5 |
| 5 | 14 | Indiranagar | Airport | completed | 1240 | 1.2 |
| 6 | 12 | MG Road | Airport | completed | 1510 | 1.8 |
| 7 | 15 | Electronic City | Koramangala | completed | 320 | 1 |
Output
| trip_id | driver_id | fare |
|---|---|---|
| 6 | 12 | 1510 |
| 1 | 11 | 1240 |
| 5 | 14 | 1240 |
How to solve Airport Drop Trips Charged With Surge Pricing
This is a pure filter over one table. Every row of the answer is a trip, so there is no join and no grouping: keep the rows where all three conditions are true — status = 'completed', drop_zone = 'Airport' and surge_multiplier > 1.0 — combined with AND.
Two details decide whether the answer is right. First, the comparison on the multiplier must be strict: a trip at exactly 1.0 paid the normal fare and is not surged. Second, the drop zone must be the airport; a trip from the airport (pickup zone) is a different question and must not slip in.
The order is fixed by the statement, fare descending, and because two trips can cost the same the tie needs its own key, trip_id ascending — without it two equal fares could come back in either order. An index on (drop_zone, status) would let the database read only airport trips; otherwise it is one scan of the table.
Reference solution (MySQL)
SELECT trip_id, driver_id, fare
FROM Trip
WHERE status = 'completed'
AND drop_zone = 'Airport'
AND surge_multiplier > 1.0
ORDER BY fare DESC, trip_idAnother way
SELECT trip_id, driver_id, fare FROM Trip WHERE drop_zone IN ('Airport') AND status <> 'cancelled' AND NOT (surge_multiplier <= 1.0) ORDER BY fare DESC, trip_id ASC← Creators Booked by Two Rival Brands at Once · Drivers Without a Single Completed Trip in March 2024 →