Delivery Confirmation Method

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

WITH delivered AS (
  -- Delivered carrier scans only (one day of padding each side for store time zones); in-transit, returned and lost shipments produce no row, so they stay null
  SELECT
    sc.shipment_id,
    s.order_id,
    sc.carrier_code,
    NULLIF(TRIM(sc.pod_code), '') AS pod_code,
    sc.scan_timestamp,
    s.store_time_zone
  FROM `your-project.erp.carrier_scans` sc
  JOIN `your-project.erp.shipments` s
    ON s.shipment_id = sc.shipment_id
  WHERE sc.scan_type = 'delivered'
    AND sc.scan_timestamp >= TIMESTAMP(DATE_SUB(start_date, INTERVAL 1 DAY))
    AND sc.scan_timestamp < TIMESTAMP(DATE_ADD(end_date, INTERVAL 2 DAY))
),
latest AS (
  -- One row per shipment: the last delivered scan by OMS scan timestamp wins, earlier corrected scans are discarded
  SELECT *
  FROM delivered
  QUALIFY ROW_NUMBER() OVER (PARTITION BY shipment_id ORDER BY scan_timestamp DESC) = 1
),
mapped AS (
  -- Raw carrier POD code mapped to the governed list; a delivered scan with a null or blank code is none, an unmapped code becomes other
  SELECT
    l.order_id,
    l.shipment_id,
    DATE(l.scan_timestamp, l.store_time_zone) AS delivery_date,
    CASE
      WHEN l.pod_code IS NULL THEN 'none'
      ELSE COALESCE(m.delivery_confirmation_method, 'other')
    END AS delivery_confirmation_method
  FROM latest l
  LEFT JOIN `your-project.erp.pod_code_map` m
    ON m.carrier_code = l.carrier_code
   AND LOWER(m.raw_pod_code) = LOWER(l.pod_code)
)
-- Shipment-level method code on the store-local delivery date, with the order-level rollup (mixed when shipments differ)
SELECT
  delivery_date,
  order_id,
  shipment_id,
  delivery_confirmation_method,
  CASE
    WHEN MIN(delivery_confirmation_method) OVER (PARTITION BY order_id)
      <> MAX(delivery_confirmation_method) OVER (PARTITION BY order_id)
    THEN 'mixed'
    ELSE delivery_confirmation_method
  END AS order_delivery_confirmation_method
FROM mapped
WHERE delivery_date BETWEEN start_date AND end_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Relative reporting window: last 90 complete days
  SELECT CAST(CURRENT_DATE - INTERVAL '90' DAY AS DATE) AS start_date,
         CAST(CURRENT_DATE - INTERVAL '1' DAY AS DATE) AS end_date
),
delivered AS (
  -- Delivered carrier scans only (one day of padding each side for store time zones); in-transit, returned and lost shipments produce no row, so they stay null
  SELECT
    sc.shipment_id,
    s.order_id,
    sc.carrier_code,
    NULLIF(TRIM(sc.pod_code), '') AS pod_code,
    sc.scan_timestamp,
    s.store_time_zone
  FROM erp_carrier_scans sc
  JOIN erp_shipments s
    ON s.shipment_id = sc.shipment_id
  CROSS JOIN params p
  WHERE sc.scan_type = 'delivered'
    AND sc.scan_timestamp >= CAST(p.start_date - INTERVAL '1' DAY AS TIMESTAMP)
    AND sc.scan_timestamp < CAST(p.end_date + INTERVAL '2' DAY AS TIMESTAMP)
),
latest AS (
  -- One row per shipment: the last delivered scan by OMS scan timestamp wins, earlier corrected scans are discarded
  SELECT *
  FROM (
    SELECT d.*, ROW_NUMBER() OVER (PARTITION BY shipment_id ORDER BY scan_timestamp DESC) AS rn
    FROM delivered d
  ) r
  WHERE rn = 1
),
mapped AS (
  -- Raw carrier POD code mapped to the governed list; a delivered scan with a null or blank code is none, an unmapped code becomes other
  SELECT
    l.order_id,
    l.shipment_id,
    CAST(FROM_UTC_TIMESTAMP(l.scan_timestamp, l.store_time_zone) AS DATE) AS delivery_date,
    CASE
      WHEN l.pod_code IS NULL THEN 'none'
      ELSE COALESCE(m.delivery_confirmation_method, 'other')
    END AS delivery_confirmation_method
  FROM latest l
  LEFT JOIN erp_pod_code_map m
    ON m.carrier_code = l.carrier_code
   AND LOWER(m.raw_pod_code) = LOWER(l.pod_code)
)
-- Shipment-level method code on the store-local delivery date, with the order-level rollup (mixed when shipments differ)
SELECT
  mp.delivery_date,
  mp.order_id,
  mp.shipment_id,
  mp.delivery_confirmation_method,
  CASE
    WHEN MIN(mp.delivery_confirmation_method) OVER (PARTITION BY mp.order_id)
      <> MAX(mp.delivery_confirmation_method) OVER (PARTITION BY mp.order_id)
    THEN 'mixed'
    ELSE mp.delivery_confirmation_method
  END AS order_delivery_confirmation_method
FROM mapped mp
CROSS JOIN params p
WHERE mp.delivery_date BETWEEN p.start_date AND p.end_date;