Benchmark Store OSA Declines

CREATE TABLE pura_vida_foods_dev.inventory_mart.mart_store_daily_availability (
  store_id STRING,
  region_id STRING,
  business_date DATE,
  osa_numerator_in_stock INT,
  osa_denominator_assortment INT,
  shelf_oos_events INT
);

WITH store_period AS (
  SELECT
    store_id,
    region_id,
    period_label,
    CASE
      WHEN SUM(osa_denominator_assortment)=0 THEN NULL
      ELSE SUM(osa_numerator_in_stock)*1.0/SUM(osa_denominator_assortment)
    END AS osa_rate
  FROM (
    SELECT
      store_id,
      region_id,
      business_date,
      osa_numerator_in_stock,
      osa_denominator_assortment,
      CASE
        WHEN business_date BETWEEN string">'{start_date}' AND string">'{end_date}' THEN string">'current'
        WHEN business_date BETWEEN date_add(string">'{start_date}', -datediff(string">'{end_date}',string">'{start_date}')-1)
                           AND date_add(string">'{end_date}',   -datediff(string">'{end_date}',string">'{start_date}')-1)
        THEN string">'prior'
        ELSE NULL
      END AS period_label
    FROM pura_vida_foods_dev.inventory_mart.mart_store_daily_availability
    WHERE region_id = string">'{region_id}'
      AND (
        business_date BETWEEN string">'{start_date}' AND string">'{end_date}'
        OR business_date BETWEEN date_add(string">'{start_date}', -datediff(string">'{end_date}',string">'{start_date}')-1)
                           AND date_add(string">'{end_date}',   -datediff(string">'{end_date}',string">'{start_date}')-1)
      )
  ) d
  WHERE period_label IS NOT NULL
  GROUP BY store_id, region_id, period_label
),
median_period AS (
  SELECT
    region_id,
    period_label,
    median(osa_rate) AS district_median_osa
  FROM store_period
  GROUP BY region_id, period_label
),
focus AS (
  SELECT * FROM store_period WHERE store_id = string">'{store_id}'
)
SELECT
  f.store_id,
  f.region_id,
  f.period_label,
  f.osa_rate,
  m.district_median_osa,
  (f.osa_rate - m.district_median_osa) 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;