Definitions
The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.On-time delivery
on_time_delivery unit: shipments no canonical variant: Ask returns all 2Deliveries that were on time. Customer Service measures against the date promised to the customer; Carrier Relations measures the carrier against its own schedule. A shipment can pass one and fail the other, so both answers are shown.
customerCustomer Service Lead · cs
v1
Shipments whose last delivery in the period (for sea, the last container) was at or before promised_delivery_at.
latest change: First version.
- pass count_betweenobserved 2554, expected between 500.0 and 6000.0
SQL, rendered for now
WITH delivered AS ( SELECT e.shipment_id, max(e."at") AS delivered_at, count(DISTINCT e.container_id) AS boxes FROM shipment_events e WHERE e.type = 'delivered' AND e."at" <= TIMESTAMP 'Tue 18:00:00' GROUP BY e.shipment_id ) SELECT s.id AS shipment_id, s.ref, s.mode, d.delivered_at, s.promised_delivery_at FROM delivered d JOIN shipments s ON s.id = d.shipment_id WHERE d.delivered_at >= TIMESTAMP 'Tue 18:00:00 (7 days ago)' AND d.delivered_at < TIMESTAMP 'Tue 18:00:00' AND (s.mode <> 'sea' OR d.boxes >= s.n_containers) AND d.delivered_at <= s.promised_delivery_at ORDER BY s.id
carrierCarrier Relations Lead · carrier_relations
v1
Sea shipments that arrived in the period within carrier_slack_hours of the carrier's scheduled arrival, whatever was promised to the customer.
latest change: First version.
- pass count_betweenobserved 1745, expected between 300.0 and 6000.0
SQL, rendered for now
SELECT s.id AS shipment_id, s.ref, e."at" AS arrived_at, s.eta_scheduled FROM shipment_events e JOIN shipments s ON s.id = e.shipment_id WHERE e.type = 'arrived' AND s.mode = 'sea' AND e."at" <= TIMESTAMP 'Tue 18:00:00' AND e."at" >= TIMESTAMP 'Tue 18:00:00 (7 days ago)' AND e."at" < TIMESTAMP 'Tue 18:00:00' AND e."at" <= s.eta_scheduled + INTERVAL (24) HOUR ORDER BY s.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- definitionCarrier Relations Lead carrier v1 First version.
- definitionCustomer Service Lead customer v1 First version.