Rank OTD Decline Drivers

CREATE TABLE ops.ops.delivery_exception_fact (
  exception_id STRING,
  shipment_id STRING,
  region_id STRING,
  carrier_id STRING,
  lane_id STRING,
  exception_code STRING,
  exception_category STRING,
  exception_ts TIMESTAMP,
  resolved_flag BOOLEAN
);

WITH scoped_shipments AS (
  SELECT
    shipment_id,
    region_id,
    carrier_id,
    lane_id,
    promised_delivery_date,
    CASE
      WHEN promised_delivery_date BETWEEN string">'{start_date}' AND string">'{end_date}' THEN string">'analysis'
      WHEN promised_delivery_date BETWEEN date_sub(string">'{start_date}', {baseline_window_days}) AND date_sub(string">'{start_date}', 1) THEN string">'baseline'
      ELSE NULL
    END AS period_label
  FROM ops.ops.shipment_delivery_fact
  WHERE region_id = string">'{region_id}'
    AND (
      promised_delivery_date BETWEEN string">'{start_date}' AND string">'{end_date}'
      OR promised_delivery_date BETWEEN date_sub(string">'{start_date}', {baseline_window_days}) AND date_sub(string">'{start_date}', 1)
    )
), exc AS (
  SELECT
    s.period_label,
    COALESCE(e.exception_category, string">'No Exception') AS exception_category,
    COUNT(DISTINCT s.shipment_id) AS shipments
  FROM scoped_shipments s
  LEFT JOIN ops.ops.delivery_exception_fact e
    ON e.shipment_id = s.shipment_id
   AND e.region_id = s.region_id
  WHERE s.period_label IS NOT NULL
  GROUP BY s.period_label, COALESCE(e.exception_category, string">'No Exception')
), totals AS (
  SELECT period_label, SUM(shipments) AS total_shipments
  FROM exc
  GROUP BY period_label
)
SELECT
  e.period_label,
  e.exception_category,
  e.shipments,
  (e.shipments / t.total_shipments) AS exception_rate
FROM exc e
JOIN totals t
  ON e.period_label = t.period_label
ORDER BY e.period_label, exception_rate DESC;