Amazon: Package Dwell Time at Each Facility Visit
A package is scanned many times as it moves through sort centres, fulfilment centres and delivery stations — and it can pass through the same facility twice…
- Difficulty: Hard
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #593
Problem statement
A package is scanned many times as it moves through sort centres, fulfilment centres and delivery stations — and it can pass through the same facility twice (for example after a missort). The network team measures how long packages sit at each stop.
For each package, take its scans in time order. A visit is a maximal run of consecutive scans at the same facility_code; a later return to the same facility is a new visit. Return one row per visit: package_id, visit_no (1 for the package's first visit, then 2, 3, …), facility_code, arrived_at (the visit's first scan) and dwell_minutes (whole minutes from the visit's first scan to its last, 0 for a single scan). Order the rows by package_id, then visit_no.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: TrackingScan
| Column | Type |
|---|---|
| package_id | varchar |
| scanned_at | datetime |
| facility_code | varchar |
Primary key: package_id, scanned_at.
One row per scan of a package; a package is never scanned twice at the same moment.
Examples
Example 1
TrackingScan
| package_id | scanned_at | facility_code |
|---|---|---|
| TBA101 | 2025-08-01 08:00:00 | BLR7 |
| TBA101 | 2025-08-01 09:30:00 | BLR7 |
| TBA101 | 2025-08-01 14:00:00 | SBLA |
| TBA101 | 2025-08-01 18:10:00 | BLR7 |
| TBA101 | 2025-08-02 07:00:00 | DBL1 |
| TBA101 | 2025-08-02 07:45:30 | DBL1 |
| TBA202 | 2025-08-01 10:00:00 | HYD3 |
| TBA202 | 2025-08-01 12:00:00 | HYD3 |
| TBA202 | 2025-08-01 16:20:00 | DHY2 |
Output
| package_id | visit_no | facility_code | arrived_at | dwell_minutes |
|---|---|---|---|---|
| TBA101 | 1 | BLR7 | 2025-08-01 08:00:00 | 90 |
| TBA101 | 2 | SBLA | 2025-08-01 14:00:00 | 0 |
| TBA101 | 3 | BLR7 | 2025-08-01 18:10:00 | 0 |
| TBA101 | 4 | DBL1 | 2025-08-02 07:00:00 | 45 |
| TBA202 | 1 | HYD3 | 2025-08-01 10:00:00 | 120 |
| TBA202 | 2 | DHY2 | 2025-08-01 16:20:00 | 0 |
How to solve Amazon: Package Dwell Time at Each Facility Visit
The tempting GROUP BY package_id, facility_code is wrong: a package that leaves BLR7 and comes back is two visits, and grouping would merge them into one with a huge dwell. The visits are islands — maximal runs of consecutive scans with the same facility — and need an identifier of their own.
The row-number trick builds one. Number each package's scans in time order (rn_all), and number them again within each facility (rn_fac). Inside one run both counters advance together, so rn_all − rn_fac is constant; when the package goes elsewhere and returns, rn_all has advanced past the scans at other facilities while rn_fac has not, so the difference jumps. Grouping by (package_id, facility_code, grp) gives each visit with its first and last scan; rn_all ≥ rn_fac always, so the difference never goes negative. A second ROW_NUMBER() by arrival time numbers the visits, and TIMESTAMPDIFF(MINUTE, …) measures the dwell, truncating partial minutes.
The alternative marks a scan as the start of a visit when LAG(facility_code) differs (or is NULL for the first scan), and a running SUM of those marks is the visit number directly. Both sort each package's scans a few times — O(n log n) — and are the standard ways to collapse runs.
Reference solution (MySQL)
WITH r AS (
SELECT package_id, scanned_at, facility_code,
ROW_NUMBER() OVER (PARTITION BY package_id ORDER BY scanned_at)
- ROW_NUMBER() OVER (PARTITION BY package_id, facility_code ORDER BY scanned_at) AS grp
FROM TrackingScan
), v AS (
SELECT package_id, facility_code, grp,
MIN(scanned_at) AS arrived_at, MAX(scanned_at) AS left_at
FROM r
GROUP BY package_id, facility_code, grp
)
SELECT package_id,
ROW_NUMBER() OVER (PARTITION BY package_id ORDER BY arrived_at) AS visit_no,
facility_code, arrived_at,
TIMESTAMPDIFF(MINUTE, arrived_at, left_at) AS dwell_minutes
FROM v
ORDER BY package_id, visit_noAnother way
WITH f AS (
SELECT package_id, scanned_at, facility_code,
CASE WHEN LAG(facility_code) OVER (PARTITION BY package_id ORDER BY scanned_at) = facility_code THEN 0 ELSE 1 END AS new_visit
FROM TrackingScan
), n AS (
SELECT package_id, scanned_at, facility_code,
SUM(new_visit) OVER (PARTITION BY package_id ORDER BY scanned_at ROWS UNBOUNDED PRECEDING) AS visit_no
FROM f
)
SELECT package_id, visit_no, MIN(facility_code) AS facility_code, MIN(scanned_at) AS arrived_at,
TIMESTAMPDIFF(MINUTE, MIN(scanned_at), MAX(scanned_at)) AS dwell_minutes
FROM n
GROUP BY package_id, visit_no
ORDER BY package_id, visit_no← Amazon: First Day Each Seller Breached the 60-Day Order Defect Rate · Amazon: Five-Star Review Bursts From Brand-New Accounts →