Definitions
The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.Rolled and the customer not told
vessel_rolled unit: shipments operator detector canonical: csThe carrier moved ("rolled") a booking to a later sailing and the customer has had no roll notice or ETA update since. The evidence lists other sailings that would still get the cargo there sooner.
A 'rolled' event at least grace_hours before now (within 21 days), the shipment has not arrived as of now, and no notification of kind roll_notice or eta_update was logged at or before now for a moment at or after the roll. The projected delivery is the new sailing's scheduled arrival plus projection_hours. Alternatives: sailings of any carrier calling at the shipment's origin (departing more than 12 hours from now) and then at its destination, arriving before the new sailing, priced from the rate card valid today for every container of the shipment.
latest change: First version.
- pass count_betweenobserved 4, expected between 0.0 and 100.0
- pass uniqueobserved {'duplicates': 0}, expected shipment_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 4}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH rolls AS (
SELECT e.shipment_id, max(e."at") AS rolled_at, arg_max(e.payload, e."at") AS payload
FROM shipment_events e
WHERE e.type = 'rolled' AND e."at" <= TIMESTAMP 'Tue 18:00:00' - INTERVAL (3) HOUR
AND e."at" >= TIMESTAMP 'Tue 18:00:00' - INTERVAL 21 DAY
GROUP BY e.shipment_id
),
open AS (
SELECT r.*, s.ref, s.shipper_id, s.origin_id, s.dest_id, s.voyage_id, s.promised_delivery_at, s.buy_rate_usd,
s.carrier_id
FROM rolls r JOIN shipments s ON s.id = r.shipment_id
WHERE NOT EXISTS (SELECT 1 FROM notifications n WHERE n.shipment_id = r.shipment_id
AND n.kind IN ('roll_notice', 'eta_update') AND n."at" >= r.rolled_at
AND n.logged_at <= TIMESTAMP 'Tue 18:00:00')
AND NOT EXISTS (SELECT 1 FROM shipment_events a WHERE a.shipment_id = r.shipment_id AND a.type = 'arrived'
AND a."at" <= TIMESTAMP 'Tue 18:00:00')
),
new_arrival AS (
SELECT o.shipment_id, min(c.sched_arrival) AS new_eta
FROM open o JOIN voyage_calls c ON c.voyage_id = o.voyage_id AND c.location_id = o.dest_id
GROUP BY o.shipment_id
),
alts AS (
SELECT o.shipment_id, v.id AS voyage_id, v.code, k.name AS carrier, dep.sched_departure AS departs,
arr.sched_arrival AS arrives,
(SELECT sum(rc.rate_usd) FROM containers c JOIN rate_card rc ON rc.carrier_id = v.carrier_id
AND rc.origin_id = o.origin_id AND rc.dest_id = o.dest_id AND rc.equipment = c.equipment
AND CAST(TIMESTAMP 'Tue 18:00:00' AS DATE) BETWEEN rc.valid_from AND rc.valid_to
WHERE c.shipment_id = o.shipment_id) AS cost_usd
FROM open o
JOIN voyage_calls dep ON dep.location_id = o.origin_id AND dep.sched_departure > TIMESTAMP 'Tue 18:00:00' + INTERVAL 12 HOUR
JOIN voyage_calls arr ON arr.voyage_id = dep.voyage_id AND arr.location_id = o.dest_id AND arr.seq > dep.seq
JOIN voyages v ON v.id = dep.voyage_id
JOIN carriers k ON k.id = v.carrier_id
JOIN new_arrival na ON na.shipment_id = o.shipment_id
WHERE v.id <> o.voyage_id AND arr.sched_arrival < na.new_eta
),
ranked AS (
SELECT a.*, row_number() OVER (PARTITION BY a.shipment_id ORDER BY a.arrives, a.cost_usd) AS k
FROM alts a WHERE a.cost_usd IS NOT NULL
)
SELECT 'shipment' AS subject_type, o.shipment_id AS subject_id, o.shipment_id, o.shipper_id,
o.rolled_at AS started_at,
CASE WHEN na.new_eta + INTERVAL (30) HOUR > o.promised_delivery_at THEN 'high'
ELSE 'medium' END AS severity,
json_object(
'shipment', o.ref, 'rolled_at', o.rolled_at,
'from_voyage', json_extract_string(o.payload, '$.from_code'),
'to_voyage', json_extract_string(o.payload, '$.to_code'),
'new_eta', na.new_eta, 'promised_delivery_at', o.promised_delivery_at,
'projected_delivery', na.new_eta + INTERVAL (30) HOUR,
'late_against_promise', na.new_eta + INTERVAL (30) HOUR > o.promised_delivery_at,
'buy_rate_usd', o.buy_rate_usd,
'alternatives', (SELECT to_json(list(struct_pack(voyage_id := r.voyage_id, code := r.code,
carrier := r.carrier, departs := r.departs, arrives := r.arrives,
cost_usd := r.cost_usd,
rate_delta_pct := round(100.0 * (r.cost_usd / nullif(o.buy_rate_usd, 0) - 1), 1))
ORDER BY r.k))
FROM ranked r WHERE r.shipment_id = o.shipment_id AND r.k <= 3),
'alternative_voyage_ids', (SELECT list(r.voyage_id ORDER BY r.k) FROM ranked r
WHERE r.shipment_id = o.shipment_id AND r.k <= 3),
'shipper', sh.name, 'account_owner', sh.account_owner
) AS evidence
FROM open o
JOIN new_arrival na ON na.shipment_id = o.shipment_id
JOIN shippers sh ON sh.id = o.shipper_id
ORDER BY started_at, o.shipment_id
Parameters
change one, see what changes in the window, then save it as a new version with a noteHistory
append-only; nothing is edited in place- definitionCustomer Service Lead cs v1 First version.