Spot BOPIS Pickup Drift

CREATE TABLE pura_vida_foods_dev.omni_mart.mart_bopis_order_fact (
  order_id STRING,
  store_id STRING,
  region_id STRING,
  order_date DATE,
  promised_ready_ts TIMESTAMP,
  actual_ready_ts TIMESTAMP,
  pickup_completed_ts TIMESTAMP,
  is_on_time_ready BOOLEAN,
  is_exception BOOLEAN
);

WITH bounds AS (
  SELECT
    string">'{start_date}'::DATE AS start_date,
    string">'{end_date}'::DATE AS end_date,
    ((string">'{end_date}'::DATE - string">'{start_date}'::DATE) + 1) AS period_days
),
store_period AS (
  SELECT
    f.store_id,
    f.region_id,
    CASE
      WHEN f.order_date BETWEEN (SELECT start_date FROM bounds) AND (SELECT end_date FROM bounds) THEN string">'current'
      WHEN f.order_date BETWEEN ((SELECT start_date FROM bounds) - (SELECT period_days FROM bounds))
                          AND ((SELECT end_date FROM bounds) - (SELECT period_days FROM bounds))
      THEN string">'prior'
      ELSE NULL
    END AS period_label,
    COUNT(*) AS orders,
    SUM(CASE WHEN f.is_on_time_ready THEN 1 ELSE 0 END) AS on_time_orders,
    SUM(CASE WHEN f.is_on_time_ready THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS on_time_rate
  FROM pura_vida_foods_dev.omni_mart.mart_bopis_order_fact f
  WHERE f.region_id = string">'{region_id}'
    AND (
      f.order_date BETWEEN (SELECT start_date FROM bounds) AND (SELECT end_date FROM bounds)
      OR f.order_date BETWEEN ((SELECT start_date FROM bounds) - (SELECT period_days FROM bounds))
                        AND ((SELECT end_date FROM bounds) - (SELECT period_days FROM bounds))
    )
  GROUP BY f.store_id, f.region_id, period_label
),
median_period AS (
  SELECT
    region_id,
    period_label,
    median(on_time_rate) AS district_median_on_time
  FROM store_period
  WHERE period_label IS NOT NULL
  GROUP BY region_id, period_label
),
focus AS (
  SELECT *
  FROM store_period
  WHERE store_id = string">'{store_id}'
    AND period_label IS NOT NULL
)
SELECT
  f.store_id,
  f.region_id,
  f.period_label,
  f.orders,
  f.on_time_orders,
  f.on_time_rate,
  m.district_median_on_time,
  (f.on_time_rate - m.district_median_on_time) AS delta_vs_median
FROM focus f
JOIN median_period m
  ON f.region_id = m.region_id
 AND f.period_label = m.period_label
ORDER BY f.period_label;