Lanes freight exceptions
World clock Tue 18:00:00 UTC held · 120×

Definitions

The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.

Duplicate booking

duplicate_booking unit: shipments operator detector canonical: ops

The same booking submitted twice: same shipper, purchase order, lane, mode and number of containers, the second within the window of the first, neither cancelled nor held. Bookings that say they are a split are left out.

opsHead of Operations · ops v1canonical

Pairs of bookings (both at or before now, the later one within the last 7 days) with the same shipper, po_number, origin, destination, mode and n_containers, the later one booked within window_minutes of the earlier one. Neither has a 'cancelled' event at or before now; the later one has no 'held' event at or before now; the later one's booking note does not start with 'split'. The later one is the subject.

latest change: First version.

  • pass count_betweenobserved 0, expected between 0.0 and 60.0
  • pass uniqueobserved {'duplicates': 0}, expected shipment_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH recent AS (
  SELECT s.* FROM shipments s
  WHERE s.booked_at <= TIMESTAMP 'Tue 18:00:00' AND s.booked_at >= TIMESTAMP 'Tue 18:00:00' - INTERVAL 7 DAY - INTERVAL (60) MINUTE
),
closed AS (
  SELECT DISTINCT e.shipment_id, e.type FROM shipment_events e
  WHERE e.type IN ('cancelled', 'held') AND e."at" <= TIMESTAMP 'Tue 18:00:00' AND e."at" >= TIMESTAMP 'Tue 18:00:00' - INTERVAL 8 DAY
)
SELECT 'shipment' AS subject_type, b.id AS subject_id, b.id AS shipment_id, b.shipper_id,
       a.id AS original_shipment_id, b.booked_at AS started_at, 'low' AS severity,
       json_object(
         'shipment', b.ref, 'original', a.ref, 'po_number', b.po_number,
         'minutes_apart', round(date_diff('second', a.booked_at, b.booked_at) / 60.0, 1),
         'same_voyage', a.voyage_id IS NOT DISTINCT FROM b.voyage_id, 'containers', b.n_containers,
         'carrier_confirmed', EXISTS (SELECT 1 FROM shipment_events e WHERE e.shipment_id = b.id
                                        AND e.type = 'booking_confirmed' AND e."at" <= TIMESTAMP 'Tue 18:00:00'),
         'booking_note', b.booking_note,
         'shipper', sh.name, 'account_owner', sh.account_owner
       ) AS evidence
FROM recent a
JOIN recent b ON b.shipper_id = a.shipper_id AND b.po_number = a.po_number AND b.origin_id = a.origin_id
  AND b.dest_id = a.dest_id AND b.mode = a.mode AND b.n_containers = a.n_containers AND b.id <> a.id
  AND b.booked_at >= a.booked_at AND b.booked_at <= a.booked_at + INTERVAL (60) MINUTE
  AND (b.booked_at > a.booked_at OR b.id > a.id)
JOIN shippers sh ON sh.id = b.shipper_id
WHERE b.booked_at >= TIMESTAMP 'Tue 18:00:00' - INTERVAL 7 DAY
  AND coalesce(b.booking_note, '') NOT ILIKE 'split%'
  AND NOT EXISTS (SELECT 1 FROM closed x WHERE x.shipment_id = a.id AND x.type = 'cancelled')
  AND NOT EXISTS (SELECT 1 FROM closed x WHERE x.shipment_id = b.id)
ORDER BY started_at, b.id

Parameters

change one, see what changes in the window, then save it as a new version with a note
now 60 minutes · used by ops

History

append-only; nothing is edited in place
  1. definitionHead of Operations ops v1 First version.