Recursive CTE — Building a Shipment Event Timeline

Delivery events are an ordered chain: picked → in_transit → out_for_delivery → delivered. A recursive CTE can traverse this chain to compute time between milestones and identify where shipments spend the most time.

-- Pivot delivery milestones into one row per shipment
WITH milestones AS (
    SELECT
        shipment_id,
        MAX(occurred_at) FILTER (WHERE status = 'picked')           AS picked_at,
        MAX(occurred_at) FILTER (WHERE status = 'in_transit')       AS in_transit_at,
        MAX(occurred_at) FILTER (WHERE status = 'out_for_delivery') AS out_for_delivery_at,
        MAX(occurred_at) FILTER (WHERE status = 'delivered')        AS delivered_at_event
    FROM lg_delivery_events
    GROUP BY shipment_id
)
SELECT
    s.order_ref,
    s.carrier,
    s.service_level,
    ROUND(EXTRACT(EPOCH FROM (m.in_transit_at - m.picked_at)) / 3600, 1)         AS pickup_to_transit_hrs,
    ROUND(EXTRACT(EPOCH FROM (m.out_for_delivery_at - m.in_transit_at)) / 3600, 1) AS transit_to_ofd_hrs,
    ROUND(EXTRACT(EPOCH FROM (m.delivered_at_event - m.out_for_delivery_at)) / 3600, 1) AS ofd_to_delivery_hrs,
    ROUND(EXTRACT(EPOCH FROM (m.delivered_at_event - m.picked_at)) / 3600, 1)    AS total_hrs
FROM milestones m
JOIN lg_shipments s ON s.id = m.shipment_id
WHERE m.picked_at IS NOT NULL AND m.delivered_at_event IS NOT NULL
ORDER BY total_hrs DESC
LIMIT 20;