Interview Question Bank · Latest row per group · filter after picking · interval cutoff
11. Packages with no scan in 48 hours
HardLogisticsQuestion #167
Concept: Latest row per group · filter after picking · interval cutoffshow
To judge something by its latest state, pick the latest row per group first (DISTINCT ON ... ORDER BY group, time DESC) and only then filter. Filtering the detail rows first changes which row is latest: drop 'delivered' scans first and a delivered package falls back to its previous scan. For an age test, compare against a fixed cutoff (snapshot - INTERVAL '48 hours'), and get total hours with EXTRACT(EPOCH ...) / 3600 rather than EXTRACT(HOUR ...), which only reads the hours field.
Example (different data):
WITH last AS ( SELECT DISTINCT ON (ticket_id) ticket_id, changed_at, status FROM ticket_history ORDER BY ticket_id, changed_at DESC ) SELECT ticket_id, changed_at FROM last WHERE status <> 'closed' AND changed_at <= TIMESTAMP '2025-02-01 09:00' - INTERVAL '7 days';
Your task
Larkspur Freight's exceptions desk chases packages that have gone quiet. It's 2025-03-31 12:00, and a package is stuck if its latest scan isn't a delivery and is 48 or more hours old.
Using each shipment's latest scan in lg_tracking_scans, return shipment_id, carrier (name), last_scan_at, last_location and hours_since_scan (hours from last_scan_at to 2025-03-31 12:00, rounded to 1 decimal) for stuck packages. Sort by hours_since_scan descending, then shipment_id.
Starting Postgres in your browser…
Checking your access…