What is on-time delivery rate?

WITH shipped AS (
  -- Snapshot the promise at ship time. Carrier estimated delivery
  -- dates move after purchase; reading today's EDD makes every late
  -- shipment look on time retroactively.
  SELECT
    label_id,
    order_id,
    carrier,
    service_level,
    shipped_at,
    promised_delivery_date
  FROM shipping_labels
  WHERE status NOT IN ('voided', 'refunded')
    AND shipped_at >= CURRENT_DATE - INTERVAL '90 days'
    AND promised_delivery_date IS NOT NULL
), outcome AS (
  SELECT
    label_id,
    MIN(scanned_at) FILTER (WHERE event_type = 'accepted')
      AS first_carrier_scan_at,
    MAX(scanned_at) FILTER (WHERE event_type = 'delivered')
      AS delivered_at,
    COUNT(*) FILTER (WHERE event_type = 'exception') AS exceptions
  FROM shipment_tracking
  GROUP BY 1
)
SELECT
  s.carrier,
  s.service_level,
  COUNT(*) AS shipments,
  -- Coverage is part of the metric, not a footnote: a 98% on-time
  -- rate over 60% coverage is not a 98% on-time rate.
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE o.delivered_at IS NOT NULL)
    / NULLIF(COUNT(*), 0), 1
  ) AS delivery_scan_coverage_pct,
  ROUND(
    100.0 * COUNT(*) FILTER (
      WHERE o.delivered_at::date <= s.promised_delivery_date
    ) / NULLIF(COUNT(*) FILTER (WHERE o.delivered_at IS NOT NULL), 0), 1
  ) AS on_time_pct,
  -- Late handoff is your problem, not the carrier's. Long hours from
  -- ship record to first carrier scan means the warehouse is behind.
  ROUND(
    AVG(EXTRACT(epoch FROM o.first_carrier_scan_at - s.shipped_at) / 3600)
      FILTER (WHERE o.first_carrier_scan_at IS NOT NULL), 1
  ) AS avg_handoff_hours,
  ROUND(
    AVG(o.delivered_at::date - s.promised_delivery_date)
      FILTER (WHERE o.delivered_at::date > s.promised_delivery_date), 1
  ) AS avg_days_late_when_late,
  ROUND(AVG(COALESCE(o.exceptions, 0)), 2) AS avg_exceptions
FROM shipped s
LEFT JOIN outcome o USING (label_id)
GROUP BY 1, 2
ORDER BY shipments DESC;