Late-shipment risk late_shipment_risk
A warehouse holding a pile of paid orders that are not picked and will miss the marketplace's ship-by time soon. One row per warehouse.
Parameters: risk_hours = 6 min_orders = 15
Test results
| meaning | test | passed | observed | expected |
|---|---|---|---|---|
| fulfilment | count_between | passed | 1 | between 0.0 and 3.0 |
| fulfilment | unique | passed | {'duplicates': 0} | warehouse_id unique |
| fulfilment | detector_shape | passed | {'missing_columns': [], 'bad_rows': 0, 'rows': 1} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |
fulfilment · v1 · head of fulfilment
Orders paid and not cancelled as of now, not yet picked, whose ship-by time falls before now plus risk_hours (or passed in the last day), not already moved to another warehouse. Listed when a warehouse holds at least min_orders of them. The evidence names the other warehouse that stocks the most of these orders' items.
WITH moved AS (
SELECT DISTINCT order_id FROM order_events WHERE type = 'rerouted' AND "at" <= {now}
),
at_risk AS (
SELECT o.id, o.warehouse_id, o.marketplace_id, o.ship_by, o.total
FROM orders o
LEFT JOIN moved mv ON mv.order_id = o.id
WHERE o.paid_at <= {now} AND (o.picked_at IS NULL OR o.picked_at > {now})
AND (o.cancelled_at IS NULL OR o.cancelled_at > {now})
AND o.ship_by <= {now} + INTERVAL ({risk_hours}) HOUR AND o.ship_by > {now} - INTERVAL 1 DAY
AND mv.order_id IS NULL
),
per_house AS (
SELECT warehouse_id, count(*) AS orders, count(*) FILTER (WHERE ship_by <= {now}) AS already_late,
min(ship_by) AS first_ship_by, round(sum(total), 2) AS value,
list(id ORDER BY ship_by)[1:100] AS order_ids,
count(*) FILTER (WHERE marketplace_id = 2) AS marketvine, count(*) FILTER (WHERE marketplace_id = 3) AS tradewick
FROM at_risk GROUP BY warehouse_id HAVING count(*) >= {min_orders}
),
cover AS (
SELECT r.warehouse_id, s.warehouse_id AS alt, count(DISTINCT r.id) AS covered
FROM at_risk r
JOIN order_lines ol ON ol.order_id = r.id
JOIN stock_levels s ON s.product_id = ol.product_id AND s.warehouse_id <> r.warehouse_id AND s.on_hand > 0
GROUP BY r.warehouse_id, s.warehouse_id
),
best AS (
SELECT warehouse_id, arg_max(alt, covered) AS to_warehouse_id, max(covered) AS covered,
list(alt ORDER BY covered DESC) AS alternative_warehouse_ids
FROM cover GROUP BY warehouse_id
)
SELECT 'warehouse' AS subject_type, w.id AS subject_id, w.id AS warehouse_id, 'warehouse:' || w.id AS scope,
p.first_ship_by - INTERVAL ({risk_hours}) HOUR AS started_at,
CASE WHEN p.already_late > 0 OR p.orders >= 60 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'warehouse', w.name, 'orders_at_risk', p.orders, 'already_late', p.already_late,
'first_ship_by', p.first_ship_by, 'value', p.value, 'order_ids', p.order_ids,
'by_marketplace', json_object('Marketvine', p.marketvine, 'Tradewick', p.tradewick),
'to_warehouse_id', b.to_warehouse_id, 'alternative_warehouse_ids', b.alternative_warehouse_ids,
'orders_coverable_there', b.covered, 'risk_hours', {risk_hours}
) AS evidence
FROM per_house p
JOIN warehouses w ON w.id = p.warehouse_id
LEFT JOIN best b ON b.warehouse_id = p.warehouse_id
ORDER BY started_at, w.id