Recursive CTE — Shipment Event Timeline Reconstruction

Delivery event logs form a sequence that must be traversed in order. This lesson uses recursive CTEs to reconstruct the full journey of a shipment and identifies where delays occurred.

1. Event Timeline for a Single Shipment

SELECT
    de.status,
    de.location,
    de.occurred_at,
    '' AS notes,
    LAG(de.occurred_at) OVER (
        PARTITION BY de.shipment_id ORDER BY de.occurred_at
    ) AS prev_event_time,
    de.occurred_at - LAG(de.occurred_at) OVER (
        PARTITION BY de.shipment_id ORDER BY de.occurred_at
    ) AS time_since_prev_event
FROM lg_delivery_events de
WHERE de.shipment_id = 1
ORDER BY de.occurred_at;

2. Longest Gap Between Events (Delay Detection)

Find where in the journey a shipment spent the most time:

WITH event_gaps AS (
    SELECT
        shipment_id,
        status,
        location,
        occurred_at,
        LAG(occurred_at) OVER (
            PARTITION BY shipment_id ORDER BY occurred_at
        ) AS prev_time,
        EXTRACT(EPOCH FROM (
            occurred_at - LAG(occurred_at) OVER (
                PARTITION BY shipment_id ORDER BY occurred_at
            )
        )) / 3600.0 AS gap_hours
    FROM lg_delivery_events
)
SELECT
    s.order_ref,
    w_o.code || ' → ' || w_d.code AS route,
    s.carrier,
    eg.status AS event_after_gap,
    eg.location,
    ROUND(eg.gap_hours, 1) AS gap_hours,
    eg.occurred_at::DATE
FROM event_gaps eg
JOIN lg_shipments s ON s.id = eg.shipment_id
JOIN lg_warehouses w_o ON w_o.id = s.warehouse_id
JOIN lg_warehouses w_d ON w_d.id = s.warehouse_id
WHERE eg.gap_hours IS NOT NULL
ORDER BY eg.gap_hours DESC
LIMIT 20;

What This Returns

tracking_number | route         | carrier | event_after_gap | gap_hours
----------------+---------------+---------+-----------------+-----------
TRK000003       | US-CH → UK-LN | DHL     | in_transit      |     115.0
TRK000009       | NL-AM → SG-01 | Maersk  | in_transit      |     115.0
...

3. Recursive CTE — Build a Shipment Chain

A recursive CTE traverses connected events. Here we build the full path of each shipment:

WITH RECURSIVE journey AS (
    -- Base: first event for each shipment
    SELECT
        de.shipment_id,
        de.id AS event_id,
        de.status,
        de.location,
        de.occurred_at,
        1 AS step,
        de.location::TEXT AS path
    FROM lg_delivery_events de
    WHERE de.id IN (
        SELECT MIN(id) FROM lg_delivery_events GROUP BY shipment_id
    )

    UNION ALL

    -- Recursive: next event for same shipment
    SELECT
        de.shipment_id,
        de.id,
        de.status,
        de.location,
        de.occurred_at,
        j.step + 1,
        j.path || ' → ' || de.location
    FROM lg_delivery_events de
    JOIN journey j ON j.shipment_id = de.shipment_id
        AND de.occurred_at > j.occurred_at
        AND de.id = (
            SELECT MIN(id)
            FROM lg_delivery_events
            WHERE shipment_id = de.shipment_id AND occurred_at > j.occurred_at
        )
    WHERE j.step < 10
)
SELECT
    shipment_id,
    MAX(step)   AS total_events,
    MAX(path)   AS full_path
FROM journey
GROUP BY shipment_id
ORDER BY total_events DESC
LIMIT 10;

4. Average Events per Shipment by Status

SELECT
    CASE WHEN s.delivered_at IS NULL THEN 'in-transit' ELSE 'delivered' END,
    COUNT(DISTINCT s.id)                                    AS shipments,
    COUNT(de.id)                                            AS total_events,
    ROUND(COUNT(de.id)::NUMERIC / NULLIF(COUNT(DISTINCT s.id), 0), 1) AS avg_events_per_shipment,
    COUNT(de.id) FILTER (WHERE de.status = 'exception') AS exception_events,
    CASE WHEN s.delivered_at IS NULL THEN 'in-transit' ELSE 'delivered' END AS state
FROM lg_shipments s
LEFT JOIN lg_delivery_events de ON de.shipment_id = s.id
GROUP BY state
ORDER BY avg_events_per_shipment DESC;

Key Takeaway

The LAG(occurred_at) OVER (PARTITION BY shipment_id ORDER BY occurred_at) pattern reconstructs event sequences without a recursive CTE for simple gap calculations. Use a full recursive CTE when you need the accumulated path or when traversal depth is variable. The step < 10 guard in the recursive CTE prevents infinite loops — always include a depth limit when the termination condition is data-dependent.

← Previous Next →