Carrier delay cluster on one lane carrier_delay_cluster
Many shipments on one lane (warehouse to region, one carrier) getting delay scans at once: a depot or a route is backed up, not one parcel.
Parameters: lookback_hours = 6 min_delayed = 8 min_share = 0.08
logistics · v1 · logistics lead
In the last lookback_hours, at least min_delayed shipments on the lane got a delay scan, and they are at least min_share of the lane's parcels in flight (collected in the last 24 hours, not delivered as of now). A lane switched to its backup carrier in the last 24 hours is not listed. The evidence shows the delay share on the other lanes into the same region: weather delays every lane into a region a little, a depot problem delays one lane a lot.
WITH delays AS (
SELECT lane_id, count(DISTINCT order_id) AS delayed, min("at") AS first_delay,
list(DISTINCT order_id) AS order_ids, mode(detail) AS detail
FROM carrier_events
WHERE type = 'delay' AND "at" <= {now} AND "at" > {now} - INTERVAL ({lookback_hours}) HOUR
GROUP BY lane_id
),
flight AS (
SELECT lane_id, count(*) AS in_flight
FROM orders
WHERE shipped_at <= {now} AND shipped_at > {now} - INTERVAL 24 HOUR
AND (delivered_at IS NULL OR delivered_at > {now})
GROUP BY lane_id
),
share AS (
SELECT d.lane_id, d.delayed, d.first_delay, d.order_ids, d.detail, f.in_flight,
d.delayed / greatest(f.in_flight, d.delayed) AS share
FROM delays d JOIN flight f ON f.lane_id = d.lane_id
),
switched AS (
SELECT lane_id FROM lane_events WHERE "at" <= {now} AND "at" > {now} - INTERVAL 24 HOUR
GROUP BY lane_id HAVING arg_max(type, "at") = 'carrier_switched'
),
region AS (
SELECT ln.region_id, s.lane_id, s.share FROM share s JOIN lanes ln ON ln.id = s.lane_id
)
SELECT 'lane' AS subject_type, ln.id AS subject_id, ln.id AS lane_id, ln.warehouse_id, ln.region_id,
'lane:' || ln.id AS scope, s.first_delay AS started_at,
CASE WHEN s.share >= 0.4 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'lane', w.name || ' → ' || rg.name, 'carrier', c.name, 'carrier_id', ln.carrier_id,
'to_carrier_id', ln.backup_carrier_id, 'backup_carrier', bc.name,
'delayed_shipments', s.delayed, 'in_flight', s.in_flight, 'delay_share', round(s.share, 3),
'scan_detail', s.detail, 'order_ids', s.order_ids[1:100],
'other_lanes_into_region_share', (SELECT round(avg(r.share), 3) FROM region r
WHERE r.region_id = ln.region_id AND r.lane_id <> ln.id),
'lookback_hours', {lookback_hours}
) AS evidence
FROM share s
JOIN lanes ln ON ln.id = s.lane_id
JOIN warehouses w ON w.id = ln.warehouse_id
JOIN regions rg ON rg.id = ln.region_id
JOIN carriers c ON c.id = ln.carrier_id
JOIN carriers bc ON bc.id = ln.backup_carrier_id
LEFT JOIN switched sw ON sw.lane_id = ln.id
WHERE s.delayed >= {min_delayed} AND s.share >= {min_share} AND sw.lane_id IS NULL
ORDER BY started_at, ln.id