Latest Tracking Scan for Each Parcel

Medium
310.

Latest Tracking Scan for Each Parcel

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

ColumnTypeValues
scan_idint
awbbigint
hub_codevarchar
scan_typeenumpicked_up, in_hub, out_for_delivery, delivered, failed_attempt
scanned_atdatetime

Primary key: scan_id.

awb is the parcel's 12-digit waybill number; hub_code the scanning facility.

Example

The rows must come back in the order shown.

TrackingScan

scan_idawbhub_codescan_typescanned_at
1900100200301BLR-HSKpicked_up2024-09-01 10:00:00
2900100200301BLR-HSKin_hub2024-09-01 18:30:00
3900100200302MUM-BHWpicked_up2024-09-01 11:15:00
4900100200301PNQ-CHKin_hub2024-09-02 06:10:00
5900100200302MUM-BHWin_hub2024-09-01 20:00:00
6900100200302MUM-ANDout_for_delivery2024-09-01 20:00:00
7900100200303DEL-GGNpicked_up2024-09-02 09:45:00

Output

awbhub_codescan_typescanned_at
900100200301PNQ-CHKin_hub2024-09-02 06:10:00
900100200302MUM-ANDout_for_delivery2024-09-01 20:00:00
900100200303DEL-GGNpicked_up2024-09-02 09:45:00
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.

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

ColumnType
scan_idint
awbbigint
hub_codevarchar
scan_typeenum(picked_up, in_hub, out_for_delivery, delivered, failed_attempt)
scanned_atdatetime

Primary key: scan_id.

awb is the parcel's 12-digit waybill number; hub_code the scanning facility.

Examples

Example 1

TrackingScan

scan_idawbhub_codescan_typescanned_at
1900100200301BLR-HSKpicked_up2024-09-01 10:00:00
2900100200301BLR-HSKin_hub2024-09-01 18:30:00
3900100200302MUM-BHWpicked_up2024-09-01 11:15:00
4900100200301PNQ-CHKin_hub2024-09-02 06:10:00
5900100200302MUM-BHWin_hub2024-09-01 20:00:00
6900100200302MUM-ANDout_for_delivery2024-09-01 20:00:00
7900100200303DEL-GGNpicked_up2024-09-02 09:45:00

Output

awbhub_codescan_typescanned_at
900100200301PNQ-CHKin_hub2024-09-02 06:10:00
900100200302MUM-ANDout_for_delivery2024-09-01 20:00:00
900100200303DEL-GGNpicked_up2024-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 awb

Another 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.awb

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