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
| Column | Type |
|---|---|
| entry_id | int |
| driver_id | int |
| terminal | varchar |
| joined_at | datetime |
| outcome | enum(dispatched, left_queue, expired) |
| wait_minutes | int |
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_id | driver_id | terminal | joined_at | outcome | wait_minutes |
|---|---|---|---|---|---|
| 1 | 7 | BLR T1 | 2024-08-01 05:10:00 | dispatched | 42 |
| 2 | 8 | BLR T1 | 2024-08-01 05:20:00 | dispatched | 55 |
| 3 | 9 | BLR T2 | 2024-08-01 05:25:00 | left_queue | 80 |
| 4 | 10 | BLR T2 | 2024-08-01 05:40:00 | dispatched | 35 |
| 5 | 7 | BLR T1 | 2024-08-01 07:00:00 | expired | 120 |
| 6 | 11 | DEL T3 | 2024-08-01 06:00:00 | left_queue | 15 |
Output
| terminal | dispatched | longest_wait |
|---|---|---|
| BLR T1 | 2 | 55 |
| BLR T2 | 1 | 35 |
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 terminalAnother 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 →