Drill 11 of 12 in Logistics questions
0

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…