Uber: Airport Queue Position When Each Driver Arrived

Medium
986.

Uber: Airport Queue Position When Each Driver Arrived

The airport app tells a driver their place in the queue the moment they enter the holding lot. The operations team wants to rebuild that number from the queue log to audit it.

A driver's queue_position on arrival is 1 plus the number of drivers at the same airport who joined earlier and were still waiting at that moment — that is, whose left_at is after this driver's joined_at (a driver leaving at the very same second is already gone). Return entry_id, driver_id and queue_position for every entry, ordered by entry_id.

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

ColumnType
entry_idint
driver_idint
airportvarchar
joined_atdatetime
left_atdatetime

Primary key: entry_id.

One row per time a driver entered an airport queue; left_at is when they were dispatched or gave up, always after joined_at. No two entries at the same airport share a joined_at.

Example

The rows must come back in the order shown.

QueueEntry

entry_iddriver_idairportjoined_atleft_at
121BLR2024-08-01 05:00:002024-08-01 05:40:00
222BLR2024-08-01 05:10:002024-08-01 05:30:00
323BLR2024-08-01 05:20:002024-08-01 06:10:00
424BLR2024-08-01 05:30:002024-08-01 05:50:00
525BLR2024-08-01 06:00:002024-08-01 06:20:00
631DEL2024-08-01 05:15:002024-08-01 05:45:00

Output

entry_iddriver_idqueue_position
1211
2222
3233
4243
5252
6311
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 Position When Each Driver Arrived

The airport app tells a driver their place in the queue the moment they enter the holding lot.

  • Difficulty: Medium
  • Topics: Subqueries, Joins, Dates
  • Dialect: MySQL
  • Problem: #986

Problem statement

The airport app tells a driver their place in the queue the moment they enter the holding lot. The operations team wants to rebuild that number from the queue log to audit it.

A driver's queue_position on arrival is 1 plus the number of drivers at the same airport who joined earlier and were still waiting at that moment — that is, whose left_at is after this driver's joined_at (a driver leaving at the very same second is already gone). Return entry_id, driver_id and queue_position for every entry, ordered by entry_id.

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

ColumnType
entry_idint
driver_idint
airportvarchar
joined_atdatetime
left_atdatetime

Primary key: entry_id.

One row per time a driver entered an airport queue; left_at is when they were dispatched or gave up, always after joined_at. No two entries at the same airport share a joined_at.

Examples

Example 1

QueueEntry

entry_iddriver_idairportjoined_atleft_at
121BLR2024-08-01 05:00:002024-08-01 05:40:00
222BLR2024-08-01 05:10:002024-08-01 05:30:00
323BLR2024-08-01 05:20:002024-08-01 06:10:00
424BLR2024-08-01 05:30:002024-08-01 05:50:00
525BLR2024-08-01 06:00:002024-08-01 06:20:00
631DEL2024-08-01 05:15:002024-08-01 05:45:00

Output

entry_iddriver_idqueue_position
1211
2222
3233
4243
5252
6311

How to solve Uber: Airport Queue Position When Each Driver Arrived

The position depends on the state of the queue at one instant, the moment this driver joined. A driver o is ahead in the queue at that instant if they joined earlier (o.joined_at < q.joined_at) and had not yet left (o.left_at > q.joined_at), at the same airport. Counting those drivers and adding one gives the position.

The solution does the count in a correlated scalar subquery, evaluated once per entry. COUNT(*) over no rows is 0, so a driver who arrives to an empty lot is position 1. The strict > on left_at implements the rule that a driver dispatched at the very second another arrives is already gone — in the example, driver 22 left at 05:30, exactly when driver 24 joined, so driver 24 is third, behind 21 and 23, not fourth.

The alternative is a LEFT JOIN of the table to itself on the same condition, grouped per entry: COUNT(o.entry_id) counts only real matches, never the NULL row an unmatched entry keeps. Both are quadratic in the entries of one airport, which is fine for a day's log; an index on (airport, joined_at) narrows each lookup.

Reference solution (MySQL)

SELECT q.entry_id, q.driver_id,
       1 + (SELECT COUNT(*) FROM QueueEntry o
            WHERE o.airport = q.airport
              AND o.joined_at < q.joined_at
              AND o.left_at > q.joined_at) AS queue_position
FROM QueueEntry q
ORDER BY q.entry_id

Another way

SELECT q.entry_id, q.driver_id, 1 + COUNT(o.entry_id) AS queue_position
FROM QueueEntry q
LEFT JOIN QueueEntry o
  ON o.airport = q.airport AND o.joined_at < q.joined_at AND o.left_at > q.joined_at
GROUP BY q.entry_id, q.driver_id
ORDER BY q.entry_id

← Uber: Promo Abuse — One Device, Many Accounts, Same Campaign · Uber: Rider Referrals That Qualified for the Bonus →