Pickups Completed

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Reporting window: the last 90 complete days, dated by handoff in the store's local time zone
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Earliest store-confirmed full or partial POS handoff per order on an in-scope pickup method (mapped to the fulfillment_method reporting values), read 7 days before the window so earlier handoffs of the same order are seen; associate retries, offline replays, duplicate scans and second pickup groups collapse, customer check-ins never qualify
WITH first_handoff AS (
  SELECT
    transaction_id,
    store_id,
    CASE pickup_method WHEN 'in_store' THEN 'pickup_in_store' WHEN 'curbside' THEN 'curbside_pickup' WHEN 'locker' THEN 'locker_pickup' END AS fulfillment_method,
    handoff_ts
  FROM `your-project.pos.pickup_handoffs`
  WHERE DATE(handoff_ts) BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND DATE_ADD(end_date, INTERVAL 1 DAY)
    AND transaction_id IS NOT NULL
    AND confirmation_type IN ('associate_scan', 'locker_release', 'curbside_checkout')
    AND pickup_method IN ('in_store', 'curbside', 'locker')
    AND pickup_status IN ('full', 'partial')
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY handoff_ts) = 1
),
-- OMS orders marked Picked Up / Collected on a pickup method, excluding test, training and demo-store orders (cancelled, expired-hold and ship-to-home conversions never reach this status or method)
oms_picked_up AS (
  SELECT transaction_id, store_id, fulfillment_method, picked_up_at, store_time_zone
  FROM `your-project.oms.orders`
  WHERE order_status IN ('picked_up', 'collected')
    AND fulfillment_method IN ('pickup_in_store', 'curbside_pickup', 'locker_pickup')
    AND is_test_order IS NOT TRUE
    AND is_training_order IS NOT TRUE
    AND is_demo_store IS NOT TRUE
),
-- Reconciliation: OMS pickups without a POS handoff are added, POS handoffs without OMS confirmation within 48 hours are dropped; the handoff store and POS handoff time win when both exist
reconciled AS (
  SELECT
    o.transaction_id,
    COALESCE(e.store_id, o.store_id) AS store_id,
    COALESCE(e.fulfillment_method, o.fulfillment_method) AS fulfillment_method,
    DATE(COALESCE(e.handoff_ts, o.picked_up_at), o.store_time_zone) AS handoff_date
  FROM oms_picked_up AS o
  LEFT JOIN first_handoff AS e ON e.transaction_id = o.transaction_id
  WHERE e.transaction_id IS NULL
    OR o.picked_up_at <= TIMESTAMP_ADD(e.handoff_ts, INTERVAL 48 HOUR)
)
-- Pickups Completed per handoff date, store and fulfillment method
SELECT
  handoff_date,
  store_id,
  fulfillment_method,
  COUNT(DISTINCT transaction_id) AS pickups_completed
FROM reconciled
WHERE handoff_date BETWEEN start_date AND end_date
GROUP BY handoff_date, store_id, fulfillment_method
ORDER BY handoff_date, store_id, fulfillment_method;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Reporting window: the last 90 complete days, dated by handoff in the store's local time zone
WITH params AS (
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date
),
-- Earliest store-confirmed full or partial POS handoff per order on an in-scope pickup method (mapped to the fulfillment_method reporting values), read 7 days before the window so earlier handoffs of the same order are seen; associate retries, offline replays, duplicate scans and second pickup groups collapse, customer check-ins never qualify
first_handoff AS (
  SELECT
    transaction_id,
    store_id,
    CASE pickup_method WHEN 'in_store' THEN 'pickup_in_store' WHEN 'curbside' THEN 'curbside_pickup' WHEN 'locker' THEN 'locker_pickup' END AS fulfillment_method,
    handoff_ts
  FROM (
    SELECT h.*, ROW_NUMBER() OVER (PARTITION BY h.transaction_id ORDER BY h.handoff_ts) AS rn
    FROM pos_pickup_handoffs AS h
    CROSS JOIN params AS p
    WHERE to_date(h.handoff_ts) BETWEEN date_sub(p.start_date, 7) AND date_add(p.end_date, 1)
      AND h.transaction_id IS NOT NULL
      AND h.confirmation_type IN ('associate_scan', 'locker_release', 'curbside_checkout')
      AND h.pickup_method IN ('in_store', 'curbside', 'locker')
      AND h.pickup_status IN ('full', 'partial')
  ) AS ranked
  WHERE rn = 1
),
-- OMS orders marked Picked Up / Collected on a pickup method, excluding test, training and demo-store orders (cancelled, expired-hold and ship-to-home conversions never reach this status or method)
oms_picked_up AS (
  SELECT transaction_id, store_id, fulfillment_method, picked_up_at, store_time_zone
  FROM oms_orders
  WHERE order_status IN ('picked_up', 'collected')
    AND fulfillment_method IN ('pickup_in_store', 'curbside_pickup', 'locker_pickup')
    AND is_test_order IS NOT TRUE
    AND is_training_order IS NOT TRUE
    AND is_demo_store IS NOT TRUE
),
-- Reconciliation: OMS pickups without a POS handoff are added, POS handoffs without OMS confirmation within 48 hours are dropped; the handoff store and POS handoff time win when both exist
reconciled AS (
  SELECT
    o.transaction_id,
    COALESCE(e.store_id, o.store_id) AS store_id,
    COALESCE(e.fulfillment_method, o.fulfillment_method) AS fulfillment_method,
    to_date(from_utc_timestamp(COALESCE(e.handoff_ts, o.picked_up_at), o.store_time_zone)) AS handoff_date
  FROM oms_picked_up AS o
  LEFT JOIN first_handoff AS e ON e.transaction_id = o.transaction_id
  WHERE e.transaction_id IS NULL
    OR o.picked_up_at <= e.handoff_ts + INTERVAL 48 HOURS
)
-- Pickups Completed per handoff date, store and fulfillment method
SELECT
  r.handoff_date,
  r.store_id,
  r.fulfillment_method,
  COUNT(DISTINCT r.transaction_id) AS pickups_completed
FROM reconciled AS r
CROSS JOIN params AS p
WHERE r.handoff_date BETWEEN p.start_date AND p.end_date
GROUP BY r.handoff_date, r.store_id, r.fulfillment_method
ORDER BY r.handoff_date, r.store_id, r.fulfillment_method;