How Streaming SQL Improves On-Time Delivery Rates

On-time delivery rate is one of the most important KPIs in logistics — and one of the hardest to improve when you're measuring it from yesterday's data. Streaming SQL with RisingWave, a PostgreSQL-compatible streaming database, lets you track OTD in real time, identify the patterns causing delays as they develop, and give operations teams the visibility they need to act before SLAs are breached.

Why Real-Time OTD Tracking Matters

On-time delivery rate measures the percentage of orders delivered within the committed window. Most logistics operations measure it daily or weekly, reviewing a report of what happened in the last cycle. The problem is structural: by the time the report is available, the decisions that would have improved the result are no longer available.

A real-time OTD tracking system changes the equation. Instead of measuring outcomes after the fact, it monitors delivery progress continuously — flagging at-risk deliveries while drivers are still in the field, surfacing the root causes of developing delays, and feeding live KPIs to the dashboards that operations managers check throughout the day.

The improvement cycle shrinks from weeks to hours: operations managers see a developing pattern (say, a specific carrier consistently running late on afternoon routes), can intervene with dispatcher instructions or customer notifications, and see the effect on that day's OTD rate rather than waiting for next week's review.

How Streaming OTD Analytics Works

The architecture has three components:

1. Live delivery event stream: scan events from drivers (out for delivery, delivered, attempted, failed) flow through Kafka into RisingWave
2. Committed delivery windows: loaded from the OMS into a RisingWave table — the reference against which actual times are compared
3. Materialized views: continuously updated OTD metrics at multiple granularities — per driver, per carrier, per zone, fleet-wide

Because RisingWave maintains these views incrementally, the fleet-wide OTD rate for the current day updates within milliseconds of each delivery scan. Dashboards always show the current number, not the number from the last batch run.

Step-by-Step Tutorial

Step 1: Set Up the Data Source

Define the Kafka source for delivery scan events and the reference table for committed delivery windows:

-- Delivery scan events from mobile scanner apps
CREATE SOURCE delivery_events (
    event_id         VARCHAR,
    parcel_id        VARCHAR,
    order_id         VARCHAR,
    driver_id        VARCHAR,
    carrier_id       VARCHAR,
    zone_id          VARCHAR,
    event_type       VARCHAR,  -- 'OUT_FOR_DELIVERY','DELIVERED','ATTEMPTED','FAILED','RETURNED'
    event_time       TIMESTAMPTZ,
    latitude         DOUBLE PRECISION,
    longitude        DOUBLE PRECISION,
    failure_reason   VARCHAR
)
WITH (
    connector    = 'kafka',
    topic        = 'delivery-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Committed delivery windows from OMS
CREATE TABLE delivery_commitments (
    parcel_id          VARCHAR PRIMARY KEY,
    order_id           VARCHAR,
    customer_id        VARCHAR,
    carrier_id         VARCHAR,
    driver_id          VARCHAR,
    zone_id            VARCHAR,
    window_start       TIMESTAMPTZ,
    window_end         TIMESTAMPTZ,  -- committed delivery deadline
    priority           VARCHAR,      -- 'STANDARD','EXPRESS','SAME_DAY'
    order_value        DECIMAL(12,2)
);

Step 2: Build the Core Materialized View

Create the per-parcel delivery outcome view — the foundation for all OTD metrics:

CREATE MATERIALIZED VIEW parcel_delivery_outcomes AS
SELECT
    e.parcel_id,
    e.order_id,
    e.driver_id,
    e.carrier_id,
    e.zone_id,
    c.window_end          AS committed_by,
    c.priority,
    c.order_value,
    e.event_time          AS delivered_at,
    e.event_type          AS final_status,
    -- On-time flag
    CASE
        WHEN e.event_type = 'DELIVERED'
             AND e.event_time <= c.window_end
        THEN TRUE
        WHEN e.event_type = 'DELIVERED'
             AND e.event_time > c.window_end
        THEN FALSE
        ELSE NULL  -- not yet delivered
    END                   AS on_time,
    -- Delay in minutes (positive = late)
    CASE
        WHEN e.event_type = 'DELIVERED'
        THEN EXTRACT(EPOCH FROM (e.event_time - c.window_end)) / 60
        ELSE NULL
    END                   AS delay_minutes
FROM (
    SELECT DISTINCT ON (parcel_id)
        parcel_id, order_id, driver_id, carrier_id,
        zone_id, event_type, event_time
    FROM delivery_events
    WHERE event_type IN ('DELIVERED','ATTEMPTED','FAILED','RETURNED')
    ORDER BY parcel_id, event_time DESC
) e
JOIN delivery_commitments c ON e.parcel_id = c.parcel_id;

Step 3: Add OTD KPI Aggregations

Build the live OTD metrics at fleet, carrier, driver, and zone levels:

-- Fleet-wide OTD: today's running rate
CREATE MATERIALIZED VIEW fleet_otd_today AS
SELECT
    COUNT(*) FILTER (WHERE on_time IS NOT NULL)  AS total_resolved,
    COUNT(*) FILTER (WHERE on_time = TRUE)        AS on_time_count,
    COUNT(*) FILTER (WHERE on_time = FALSE)       AS late_count,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE on_time = TRUE)::NUMERIC /
        NULLIF(COUNT(*) FILTER (WHERE on_time IS NOT NULL), 0),
    2)                                            AS otd_rate_pct,
    AVG(delay_minutes) FILTER (WHERE on_time = FALSE) AS avg_late_minutes,
    MAX(delay_minutes) FILTER (WHERE on_time = FALSE) AS max_late_minutes
FROM parcel_delivery_outcomes
WHERE delivered_at >= DATE_TRUNC('day', NOW());

-- Per-carrier OTD: identify underperforming carriers
CREATE MATERIALIZED VIEW carrier_otd_today AS
SELECT
    carrier_id,
    COUNT(*) FILTER (WHERE on_time IS NOT NULL)  AS total_resolved,
    COUNT(*) FILTER (WHERE on_time = TRUE)        AS on_time_count,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE on_time = TRUE)::NUMERIC /
        NULLIF(COUNT(*) FILTER (WHERE on_time IS NOT NULL), 0),
    2)                                            AS otd_rate_pct,
    AVG(delay_minutes) FILTER (WHERE delay_minutes > 0) AS avg_delay_min
FROM parcel_delivery_outcomes
WHERE delivered_at >= DATE_TRUNC('day', NOW())
GROUP BY carrier_id
ORDER BY otd_rate_pct ASC;

-- Per-zone OTD: spot geographic patterns
CREATE MATERIALIZED VIEW zone_otd_today AS
SELECT
    zone_id,
    COUNT(*) FILTER (WHERE on_time IS NOT NULL)  AS total_resolved,
    COUNT(*) FILTER (WHERE on_time = TRUE)        AS on_time_count,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE on_time = TRUE)::NUMERIC /
        NULLIF(COUNT(*) FILTER (WHERE on_time IS NOT NULL), 0),
    2)                                            AS otd_rate_pct
FROM parcel_delivery_outcomes
WHERE delivered_at >= DATE_TRUNC('day', NOW())
GROUP BY zone_id;

-- Hourly OTD trend: see improvement/deterioration through the day
CREATE MATERIALIZED VIEW otd_hourly_trend AS
SELECT
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE on_time IS NOT NULL)   AS deliveries,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE on_time = TRUE)::NUMERIC /
        NULLIF(COUNT(*) FILTER (WHERE on_time IS NOT NULL), 0),
    2)                                             AS otd_rate_pct,
    AVG(delay_minutes)                             AS avg_delay_min
FROM TUMBLE(
    (SELECT p.*, e.event_time AS scan_time
     FROM parcel_delivery_outcomes p
     JOIN delivery_events e ON p.parcel_id = e.parcel_id
       AND e.event_type = 'DELIVERED'),
    scan_time,
    INTERVAL '1 hour'
)
GROUP BY window_start, window_end;

How This Compares to Traditional Approaches

FAQ

How do I build a proactive alert for deliveries at risk of missing their window?

Combine parcel_delivery_outcomes (for parcels not yet resolved) with delivery_commitments to compute which parcels are OUT_FOR_DELIVERY and have less than 30 minutes remaining before their commitment window closes. This can be a simple materialized view using WHERE event_type = 'OUT_FOR_DELIVERY' AND committed_by - NOW() < INTERVAL '30 minutes'.

Can I track OTD for a specific customer or SLA tier?

Yes. The delivery_commitments table includes customer_id and priority columns. Filter any of the OTD views by these columns in your dashboard queries, or create dedicated materialized views for high-value customer segments or express-tier deliveries that require closer monitoring.

How do I compare today's OTD rate to last week's at the same hour?

Use the otd_hourly_trend view for today and query the historical data from an Iceberg sink for the comparison period. Alternatively, use a HOP window function to compute a rolling 7-day average that updates continuously, giving you a live benchmark on your dashboard.

Key Takeaways

• RisingWave maintains live OTD metrics at fleet, carrier, driver, and zone levels with sub-second freshness — turning a lagging indicator into a real-time operational signal that dispatchers can act on the same day.
• The parcel_delivery_outcomes materialized view joins delivery scan events against committed windows continuously, so fleet_otd_today always reflects every scan made since midnight without any scheduled jobs.
• Granular views by carrier and zone surface systemic performance issues — a carrier running 15 minutes late on afternoon routes, or a zone with elevated failure rates — while there is still time to intervene in the current shift.

Ready to try this yourself? Get started with RisingWave in minutes. Join our Slack community.