Latest Tracking Scan for Each Parcel — SQL Problem
Every time a parcel passes a scanner — pickup, hub inbound, out for delivery, delivery or a failed attempt — a row is written to TrackingScan.
- Difficulty: Medium
- Topics: Subqueries, Window Functions
- Dialect: MySQL
- Problem: #310
Problem statement
Every time a parcel passes a scanner — pickup, hub inbound, out for delivery, delivery or a failed attempt — a row is written to TrackingScan. The tracking page shows each parcel's latest scan. Scanners batch-upload, so two scans of one parcel can share a timestamp; then the one with the higher scan_id is the latest.
Return awb, hub_code, scan_type and scanned_at of each parcel's latest scan, one row per parcel, ordered by awb.
Tables
Table: TrackingScan
| Column | Type |
|---|---|
| scan_id | int |
| awb | bigint |
| hub_code | varchar |
| scan_type | enum(picked_up, in_hub, out_for_delivery, delivered, failed_attempt) |
| scanned_at | datetime |
Primary key: scan_id.
awb is the parcel's 12-digit waybill number; hub_code the scanning facility.
Examples
Example 1
TrackingScan
| scan_id | awb | hub_code | scan_type | scanned_at |
|---|---|---|---|---|
| 1 | 900100200301 | BLR-HSK | picked_up | 2024-09-01 10:00:00 |
| 2 | 900100200301 | BLR-HSK | in_hub | 2024-09-01 18:30:00 |
| 3 | 900100200302 | MUM-BHW | picked_up | 2024-09-01 11:15:00 |
| 4 | 900100200301 | PNQ-CHK | in_hub | 2024-09-02 06:10:00 |
| 5 | 900100200302 | MUM-BHW | in_hub | 2024-09-01 20:00:00 |
| 6 | 900100200302 | MUM-AND | out_for_delivery | 2024-09-01 20:00:00 |
| 7 | 900100200303 | DEL-GGN | picked_up | 2024-09-02 09:45:00 |
Output
| awb | hub_code | scan_type | scanned_at |
|---|---|---|---|
| 900100200301 | PNQ-CHK | in_hub | 2024-09-02 06:10:00 |
| 900100200302 | MUM-AND | out_for_delivery | 2024-09-01 20:00:00 |
| 900100200303 | DEL-GGN | picked_up | 2024-09-02 09:45:00 |
How to solve Latest Tracking Scan for Each Parcel
This is the classic latest row per group problem. MAX(scanned_at) per awb finds the time, but not the hub and type of the row that holds it; and joining back on the time would return two rows when two scans share it.
ROW_NUMBER() OVER (PARTITION BY awb ORDER BY scanned_at DESC, scan_id DESC) numbers each parcel's scans from newest, with scan_id deciding equal timestamps, so exactly one row per parcel gets 1. Filter on it outside the subquery, since a window value cannot be used in the WHERE of the query that computes it.
Without windows, keep a scan when no other scan of the same parcel is later — NOT EXISTS with the tie rule spelt out as (later time) OR (same time AND higher id). Or pick the latest scan's id with an ordered LIMIT 1 correlated subquery. With an index on (awb, scanned_at, scan_id) all three read each parcel's newest scan directly; the window version sorts the whole table once.
Reference solution (MySQL)
SELECT awb, hub_code, scan_type, scanned_at
FROM (
SELECT awb, hub_code, scan_type, scanned_at,
ROW_NUMBER() OVER (PARTITION BY awb ORDER BY scanned_at DESC, scan_id DESC) AS rn
FROM TrackingScan
) s
WHERE rn = 1
ORDER BY awbAnother way
SELECT s.awb, s.hub_code, s.scan_type, s.scanned_at FROM TrackingScan s WHERE NOT EXISTS (SELECT 1 FROM TrackingScan t WHERE t.awb = s.awb AND (t.scanned_at > s.scanned_at OR (t.scanned_at = s.scanned_at AND t.scan_id > s.scan_id))) ORDER BY s.awbAnother way
SELECT s.awb, s.hub_code, s.scan_type, s.scanned_at FROM TrackingScan s WHERE s.scan_id = (SELECT t.scan_id FROM TrackingScan t WHERE t.awb = s.awb ORDER BY t.scanned_at DESC, t.scan_id DESC LIMIT 1) ORDER BY s.awb← Drivers Qualifying for City Trip Incentives · Monthly Completed Trips, GMV and Average Fare →