Uber: Uber Eats Orders Handed to Another Courier

Medium
982.

Uber: Uber Eats Orders Handed to Another Courier

MediumWindow Functions

MySQL

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

ColumnType
assignment_idint
order_idint
courier_idint
assigned_atdatetime

Primary key: assignment_id.

One row each time dispatch gave an order to a courier. No order has two assignments at the same second.

Example

The rows must come back in the order shown.

CourierAssignment

assignment_idorder_idcourier_idassigned_at
15001712024-10-04 19:00:00
25002722024-10-04 19:02:00
35001732024-10-04 19:09:00
45003742024-10-04 19:05:00
55003752024-10-04 19:12:00
65003742024-10-04 19:20:00
75004762024-10-04 19:15:00
85004712024-10-04 19:18:00
95004722024-10-04 19:26:00

Output

order_idfirst_courierfinal_courierassignments
500171732
500476723
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: 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

ColumnType
assignment_idint
order_idint
courier_idint
assigned_atdatetime

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_idorder_idcourier_idassigned_at
15001712024-10-04 19:00:00
25002722024-10-04 19:02:00
35001732024-10-04 19:09:00
45003742024-10-04 19:05:00
55003752024-10-04 19:12:00
65003742024-10-04 19:20:00
75004762024-10-04 19:15:00
85004712024-10-04 19:18:00
95004722024-10-04 19:26:00

Output

order_idfirst_courierfinal_courierassignments
500171732
500476723

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_id

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