Standardize Store Conversion Review

CREATE TABLE pura_vida_foods_dev.retail_mart.mart_store_daily_kpi (
  store_id STRING,
  region_id STRING,
  business_date DATE,
  traffic_visits INT,
  transactions INT,
  sales_amount DECIMAL(12,2)
);

WITH store_period AS (
  SELECT
    store_id,
    region_id,
    period_label,
    SUM(traffic_visits) AS traffic,
    SUM(transactions) AS transactions,
    CASE WHEN SUM(traffic_visits)=0 THEN NULL ELSE SUM(transactions)*1.0/SUM(traffic_visits) END AS conversion_rate
  FROM (
    SELECT
      store_id,
      region_id,
      business_date,
      traffic_visits,
      transactions,
      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.retail_mart.mart_store_daily_kpi
    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,
    quantile_cont(conversion_rate, 0.5) AS district_median_conversion
  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.traffic,
  f.transactions,
  f.conversion_rate,
  m.district_median_conversion,
  (f.conversion_rate - m.district_median_conversion) 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;