Rank Stockout Drivers Fast

CREATE TABLE pura_vida_foods_dev.supply_chain_ops.mart_demand_elasticity_weather (
  dc_id STRING,
  sku_group STRING,
  temp_feature STRING,
  elasticity_pct_per_1f DOUBLE,
  model_window_days INT,
  r2 DOUBLE,
  last_trained_date DATE
);

WITH elastic AS (
  SELECT dc_id, sku_group, elasticity_pct_per_1f, r2
  FROM pura_vida_foods_dev.supply_chain_ops.mart_demand_elasticity_weather
  WHERE sku_group IN (string">'CHILLED_BEVERAGES',string">'CHILLED_DAIRY',string">'READY_TO_EAT')
),
dc_list AS (
  SELECT CASE
    WHEN string">'{dc_scope}' = string">'strikebound_only' THEN string">'DC_CHI1'
    ELSE string">'{dc_scope}'
  END AS dc_id
  UNION ALL
  SELECT CASE
    WHEN string">'{dc_scope}' = string">'strikebound_only' THEN string">'DC_ATL2'
    ELSE NULL
  END AS dc_id
)
SELECT e.dc_id,
       e.sku_group,
       {temp_spike_f} AS temp_spike_f,
       round(100 * ({temp_spike_f} * e.elasticity_pct_per_1f), 1) AS expected_demand_uplift_pct,
       e.r2
FROM elastic e
JOIN dc_list d
  ON d.dc_id = e.dc_id
WHERE d.dc_id IS NOT NULL
ORDER BY expected_demand_uplift_pct DESC;

CREATE TABLE pura_vida_foods_dev.supply_chain_ops.mart_inventory_position_daily (
  dc_id STRING,
  as_of_date DATE,
  sku_group STRING,
  on_hand_units BIGINT,
  on_order_units BIGINT,
  safety_stock_units BIGINT,
  avg_daily_demand_units BIGINT,
  gross_margin_per_unit DOUBLE
);

WITH dc_list AS (
  SELECT CASE
    WHEN string">'{dc_scope}' = string">'strikebound_only' THEN string">'DC_CHI1'
    ELSE string">'{dc_scope}'
  END AS dc_id
  UNION ALL
  SELECT CASE
    WHEN string">'{dc_scope}' = string">'strikebound_only' THEN string">'DC_ATL2'
    ELSE NULL
  END AS dc_id
)
SELECT i.dc_id,
       i.as_of_date,
       i.sku_group,
       i.on_hand_units,
       i.on_order_units,
       i.safety_stock_units,
       i.avg_daily_demand_units,
       i.gross_margin_per_unit
FROM pura_vida_foods_dev.supply_chain_ops.mart_inventory_position_daily i
JOIN dc_list d
  ON d.dc_id = i.dc_id
WHERE d.dc_id IS NOT NULL
  AND i.as_of_date = CAST(string">'{end_date}' AS DATE)
ORDER BY i.dc_id, i.sku_group;

CREATE TABLE pura_vida_foods_dev.supply_chain_ops.tms_telematics_inbound_shipments (
  shipment_id STRING,
  dc_id STRING,
  sku_group STRING,
  planned_arrival_ts TIMESTAMP,
  current_eta_ts TIMESTAMP,
  on_time_prob DOUBLE,
  dwell_minutes_last_6h INT,
  current_status STRING,
  units BIGINT,
  last_ping_ts TIMESTAMP
);

WITH dc_list AS (
  SELECT CASE
    WHEN string">'{dc_scope}' = string">'strikebound_only' THEN string">'DC_CHI1'
    ELSE string">'{dc_scope}'
  END AS dc_id
  UNION ALL
  SELECT CASE
    WHEN string">'{dc_scope}' = string">'strikebound_only' THEN string">'DC_ATL2'
    ELSE NULL
  END AS dc_id
)
SELECT s.shipment_id,
       s.dc_id,
       s.sku_group,
       s.planned_arrival_ts,
       s.current_eta_ts,
       s.on_time_prob,
       s.dwell_minutes_last_6h,
       s.current_status,
       s.units,
       s.last_ping_ts
FROM pura_vida_foods_dev.supply_chain_ops.tms_telematics_inbound_shipments s
JOIN dc_list d
  ON d.dc_id = s.dc_id
WHERE d.dc_id IS NOT NULL
  AND s.current_eta_ts >= CAST(string">'{start_date}' AS TIMESTAMP)
  AND s.current_eta_ts <  CAST(string">'{end_date}' AS TIMESTAMP)
ORDER BY s.dc_id, s.sku_group, s.current_eta_ts;