Airport Drop Trips Charged With Surge Pricing

Easy
298.

Airport Drop Trips Charged With Surge Pricing

EasyBasics

MySQL

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

ColumnTypeValues
trip_idint
driver_idint
pickup_zonevarchar
drop_zonevarchar
statusenumcompleted, cancelled
fareint
surge_multiplierdecimal

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.

Example

The rows must come back in the order shown.

Trip

trip_iddriver_idpickup_zonedrop_zonestatusfaresurge_multiplier
111KoramangalaAirportcompleted12401.4
212WhitefieldAirportcompleted9801
313HebbalAirportcancelled01.6
411AirportMG Roadcompleted11001.5
514IndiranagarAirportcompleted12401.2
612MG RoadAirportcompleted15101.8
715Electronic CityKoramangalacompleted3201

Output

trip_iddriver_idfare
6121510
1111240
5141240
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.

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

ColumnType
trip_idint
driver_idint
pickup_zonevarchar
drop_zonevarchar
statusenum(completed, cancelled)
fareint
surge_multiplierdecimal

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_iddriver_idpickup_zonedrop_zonestatusfaresurge_multiplier
111KoramangalaAirportcompleted12401.4
212WhitefieldAirportcompleted9801
313HebbalAirportcancelled01.6
411AirportMG Roadcompleted11001.5
514IndiranagarAirportcompleted12401.2
612MG RoadAirportcompleted15101.8
715Electronic CityKoramangalacompleted3201

Output

trip_iddriver_idfare
6121510
1111240
5141240

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_id

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