Uber: Airport Queue Dispatches per Terminal

Easy
978.

Uber: Airport Queue Dispatches per Terminal

EasyAggregation

MySQL

At airports, drivers wait in a virtual queue and are dispatched to arriving passengers in turn. The airport operations team reviews each terminal's queue every morning.

For every terminal with at least one dispatched queue entry, return terminal, dispatched (the number of dispatched entries) and longest_wait (the largest wait_minutes among the dispatched entries only). Entries that left the queue or expired count for nothing. Return the rows in any order.

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: AirportQueue

ColumnTypeValues
entry_idint
driver_idint
terminalvarchar
joined_atdatetime
outcomeenumdispatched, left_queue, expired
wait_minutesint

Primary key: entry_id.

One row each time a driver joined a terminal's queue; wait_minutes is the time from joining until the outcome.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AirportQueue

entry_iddriver_idterminaljoined_atoutcomewait_minutes
17BLR T12024-08-01 05:10:00dispatched42
28BLR T12024-08-01 05:20:00dispatched55
39BLR T22024-08-01 05:25:00left_queue80
410BLR T22024-08-01 05:40:00dispatched35
57BLR T12024-08-01 07:00:00expired120
611DEL T32024-08-01 06:00:00left_queue15

Output

terminaldispatchedlongest_wait
BLR T1255
BLR T2135
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: Airport Queue Dispatches per Terminal — SQL Problem

At airports, drivers wait in a virtual queue and are dispatched to arriving passengers in turn.

  • Difficulty: Easy
  • Topics: Aggregation
  • Dialect: MySQL
  • Problem: #978

Problem statement

At airports, drivers wait in a virtual queue and are dispatched to arriving passengers in turn. The airport operations team reviews each terminal's queue every morning.

For every terminal with at least one dispatched queue entry, return terminal, dispatched (the number of dispatched entries) and longest_wait (the largest wait_minutes among the dispatched entries only). Entries that left the queue or expired count for nothing. Return the rows in any order.

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: AirportQueue

ColumnType
entry_idint
driver_idint
terminalvarchar
joined_atdatetime
outcomeenum(dispatched, left_queue, expired)
wait_minutesint

Primary key: entry_id.

One row each time a driver joined a terminal's queue; wait_minutes is the time from joining until the outcome.

Examples

Example 1

AirportQueue

entry_iddriver_idterminaljoined_atoutcomewait_minutes
17BLR T12024-08-01 05:10:00dispatched42
28BLR T12024-08-01 05:20:00dispatched55
39BLR T22024-08-01 05:25:00left_queue80
410BLR T22024-08-01 05:40:00dispatched35
57BLR T12024-08-01 07:00:00expired120
611DEL T32024-08-01 06:00:00left_queue15

Output

terminaldispatchedlongest_wait
BLR T1255
BLR T2135

How to solve Uber: Airport Queue Dispatches per Terminal

Both requested numbers describe dispatched entries, so the simplest plan is to filter first and aggregate second. WHERE outcome = 'dispatched' drops the drivers who gave up or whose entry expired; GROUP BY terminal then forms one group per terminal, and COUNT(*) and MAX(wait_minutes) read off the two columns.

Filtering before grouping matters for longest_wait: in the example, BLR T1's expired entry waited 120 minutes, but that wait never led to a passenger, so the answer is 55. A terminal with no dispatched entry, like DEL T3, loses all its rows in WHERE and so never appears — exactly what the statement wants.

The alternative keeps every row and moves the condition into the aggregates (COUNT(CASE …), MAX(CASE …) — a CASE without ELSE yields NULL, which both functions skip), then removes terminals with no dispatch in HAVING. That form is handy when one query must report several outcomes side by side. Both are a single pass over the queue.

Reference solution (MySQL)

SELECT terminal, COUNT(*) AS dispatched, MAX(wait_minutes) AS longest_wait
FROM AirportQueue
WHERE outcome = 'dispatched'
GROUP BY terminal

Another way

SELECT terminal,
       COUNT(CASE WHEN outcome = 'dispatched' THEN 1 END) AS dispatched,
       MAX(CASE WHEN outcome = 'dispatched' THEN wait_minutes END) AS longest_wait
FROM AirportQueue
GROUP BY terminal
HAVING COUNT(CASE WHEN outcome = 'dispatched' THEN 1 END) > 0

← Uber: Driver Names as Shown in the Rider App · Uber: Riders Who Gave Every Rated Trip Five Stars →