Uber: Uber Eats Orders Handed to Another Courier
When an Uber Eats courier drops an order (a flat tyre, a long wait at the restaurant), dispatch reassigns it.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #982
Problem statement
When an Uber Eats courier drops an order (a flat tyre, a long wait at the restaurant), dispatch reassigns it. The delivery-experience team wants the orders that ended up with a different courier from the one first assigned, because those are the late, cold ones.
An order's first courier is the one on its earliest assignment and its final courier the one on its latest (assigned_at). Return order_id, first_courier, final_courier and assignments (the number of assignment rows of the order) for every order whose final courier is different from its first courier. An order that went away and came back to its first courier is not in the answer. Order by order_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: CourierAssignment
| Column | Type |
|---|---|
| assignment_id | int |
| order_id | int |
| courier_id | int |
| assigned_at | datetime |
Primary key: assignment_id.
One row each time dispatch gave an order to a courier. No order has two assignments at the same second.
Examples
Example 1
CourierAssignment
| assignment_id | order_id | courier_id | assigned_at |
|---|---|---|---|
| 1 | 5001 | 71 | 2024-10-04 19:00:00 |
| 2 | 5002 | 72 | 2024-10-04 19:02:00 |
| 3 | 5001 | 73 | 2024-10-04 19:09:00 |
| 4 | 5003 | 74 | 2024-10-04 19:05:00 |
| 5 | 5003 | 75 | 2024-10-04 19:12:00 |
| 6 | 5003 | 74 | 2024-10-04 19:20:00 |
| 7 | 5004 | 76 | 2024-10-04 19:15:00 |
| 8 | 5004 | 71 | 2024-10-04 19:18:00 |
| 9 | 5004 | 72 | 2024-10-04 19:26:00 |
Output
| order_id | first_courier | final_courier | assignments |
|---|---|---|---|
| 5001 | 71 | 73 | 2 |
| 5004 | 76 | 72 | 3 |
How to solve Uber: Uber Eats Orders Handed to Another Courier
Each order needs two values taken from particular rows: the courier on its earliest assignment and the courier on its latest. FIRST_VALUE(courier_id) and LAST_VALUE(courier_id) over PARTITION BY order_id ORDER BY assigned_at read exactly those, and COUNT(*) OVER (PARTITION BY order_id) adds the size of the order's history to every row.
The frame is the trap. With an ORDER BY and no explicit frame, a window runs from the start of the partition to the current row, so LAST_VALUE would just return the current row's courier. Writing ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING makes every row see the whole order, so all rows of one order carry the same three values, and SELECT DISTINCT folds them into one.
The filter compares couriers, not counts. Order 5003 was assigned three times but ended with courier 74, who also had it first, so it is not a handover; order 5002, assigned once, cannot differ from itself. The alternative finds the first and last times with MIN/MAX per order and joins back to the two assignment rows — safe because no order has two assignments at the same second. The window version sorts each order once; the join version needs an index on (order_id, assigned_at) to stay cheap.
Reference solution (MySQL)
WITH framed AS (
SELECT order_id,
FIRST_VALUE(courier_id) OVER (PARTITION BY order_id ORDER BY assigned_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_courier,
LAST_VALUE(courier_id) OVER (PARTITION BY order_id ORDER BY assigned_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_courier,
COUNT(*) OVER (PARTITION BY order_id) AS assignments
FROM CourierAssignment
)
SELECT DISTINCT order_id, first_courier, final_courier, assignments
FROM framed
WHERE first_courier <> final_courier
ORDER BY order_idAnother way
SELECT b.order_id, f.courier_id AS first_courier, l.courier_id AS final_courier, b.assignments
FROM (SELECT order_id, MIN(assigned_at) AS first_at, MAX(assigned_at) AS last_at, COUNT(*) AS assignments
FROM CourierAssignment GROUP BY order_id) b
JOIN CourierAssignment f ON f.order_id = b.order_id AND f.assigned_at = b.first_at
JOIN CourierAssignment l ON l.order_id = b.order_id AND l.assigned_at = b.last_at
WHERE f.courier_id <> l.courier_id
ORDER BY b.order_id← Uber: Surge Share of Bengaluru Trips by Hour of Day · Uber: Pickup ETA Accuracy by City →