Amazon: Package Dwell Time at Each Facility Visit

Hard
593.

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 (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

ColumnType
package_idvarchar
scanned_atdatetime
facility_codevarchar

Primary key (together): package_id, scanned_at.

One row per scan of a package; a package is never scanned twice at the same moment.

Example

The rows must come back in the order shown.

TrackingScan

package_idscanned_atfacility_code
TBA1012025-08-01 08:00:00BLR7
TBA1012025-08-01 09:30:00BLR7
TBA1012025-08-01 14:00:00SBLA
TBA1012025-08-01 18:10:00BLR7
TBA1012025-08-02 07:00:00DBL1
TBA1012025-08-02 07:45:30DBL1
TBA2022025-08-01 10:00:00HYD3
TBA2022025-08-01 12:00:00HYD3
TBA2022025-08-01 16:20:00DHY2

Output

package_idvisit_nofacility_codearrived_atdwell_minutes
TBA1011BLR72025-08-01 08:00:0090
TBA1012SBLA2025-08-01 14:00:000
TBA1013BLR72025-08-01 18:10:000
TBA1014DBL12025-08-02 07:00:0045
TBA2021HYD32025-08-01 10:00:00120
TBA2022DHY22025-08-01 16:20:000
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.

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

ColumnType
package_idvarchar
scanned_atdatetime
facility_codevarchar

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_idscanned_atfacility_code
TBA1012025-08-01 08:00:00BLR7
TBA1012025-08-01 09:30:00BLR7
TBA1012025-08-01 14:00:00SBLA
TBA1012025-08-01 18:10:00BLR7
TBA1012025-08-02 07:00:00DBL1
TBA1012025-08-02 07:45:30DBL1
TBA2022025-08-01 10:00:00HYD3
TBA2022025-08-01 12:00:00HYD3
TBA2022025-08-01 16:20:00DHY2

Output

package_idvisit_nofacility_codearrived_atdwell_minutes
TBA1011BLR72025-08-01 08:00:0090
TBA1012SBLA2025-08-01 14:00:000
TBA1013BLR72025-08-01 18:10:000
TBA1014DBL12025-08-02 07:00:0045
TBA2021HYD32025-08-01 10:00:00120
TBA2022DHY22025-08-01 16:20:000

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_no

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