mapping

CREATE OR REPLACE TABLE noonbitechit.search.crosslisting_sku_mapping
AS


WITH minutes_products AS (
  SELECT sku AS zsku, lower(os.country_code) as country_code, psku_code, nsku_child AS nsku, partner_barcode, pb.id_partner, brand_code, SUM(stock_net) as total_stock
  FROM `noondwh.instant_spanner.product` prod
  JOIN `noondwh.psku.psku` p ON prod.sku = p.zsku_child
  JOIN `noondwh.psku.pbarcode` pb USING(psku_code)
  JOIN `noondwh.instant_instant_offer.offer_stock` os USING (sku)
  WHERE (os.is_active = 1 and os.stock_net > 0) OR os.updated_at > CURRENT_TIMESTAMP() - INTERVAL 7 DAY
  GROUP BY 1, 2, 3, 4, 5, 6, 7
), core_zskus AS (
  SELECT sku AS zsku, psku_code, nsku_child AS nsku, partner_barcode, brand_code, pb.id_partner
  FROM `noondwh.cache_spanner.product` prod
  JOIN `noondwh.psku.psku` p ON prod.sku = p.zsku_child
  JOIN `noondwh.psku.pbarcode` pb USING(psku_code)
  WHERE prod.catalog_code = 'zsku'
  AND prod.updated_at > CURRENT_TIMESTAMP() - INTERVAL 365 DAY
  AND prod.is_active = true
), core_nskus AS (
  SELECT sku AS nsku, psku_code, zsku_child AS zsku, partner_barcode, brand_code, pb.id_partner
  FROM `noondwh.cache_spanner.product` prod
  JOIN `noondwh.psku.psku` p ON prod.sku = p.nsku_child
  JOIN `noondwh.psku.pbarcode` pb USING(psku_code)
  WHERE prod.catalog_code = 'noon'
  AND prod.updated_at > CURRENT_TIMESTAMP() - INTERVAL 365 DAY
  AND prod.is_active = true
), zsku_max_stock_mapping AS (
  SELECT zsku, country_code, max(total_stock) as total_stock FROM (
    SELECT core.zsku, nim.country_code, max(total_stock) as total_stock
    FROM minutes_products nim
    JOIN core_zskus core ON core.nsku = nim.nsku
    GROUP BY 1, 2
    UNION ALL
    SELECT core.zsku, nim.country_code, max(total_stock) as total_stock
    FROM minutes_products nim
    JOIN core_zskus core ON core.zsku = nim.zsku
    GROUP BY 1, 2
    UNION ALL
    SELECT core.zsku, nim.country_code, max(total_stock) as total_stock
    FROM minutes_products nim
    JOIN core_zskus core USING (partner_barcode, brand_code)
    GROUP BY 1, 2
  )
  GROUP BY 1, 2
), nsku_max_stock_mapping AS (
  SELECT nsku, country_code, max(total_stock) as total_stock FROM (
    SELECT core.nsku, nim.country_code, max(total_stock) as total_stock
    FROM minutes_products nim
    JOIN core_nskus core ON core.zsku = nim.zsku
    GROUP BY 1, 2
    UNION ALL
    SELECT core.nsku, nim.country_code, max(total_stock) as total_stock
    FROM minutes_products nim
    JOIN core_nskus core ON core.nsku = nim.nsku
    GROUP BY 1, 2
    UNION ALL
    SELECT core.nsku, nim.country_code, max(total_stock) as total_stock
    FROM minutes_products nim
    JOIN core_nskus core USING (partner_barcode, id_partner, brand_code)
    GROUP BY 1, 2
  )
  GROUP BY 1, 2
)
SELECT sku, country_code, ANY_VALUE(instant_sku) as instant_sku FROM (
  SELECT core.zsku as sku, nim.country_code, ANY_VALUE(nim.zsku) as instant_sku
  FROM core_zskus core
  JOIN minutes_products nim ON core.zsku = nim.zsku
  JOIN zsku_max_stock_mapping msm ON core.zsku = msm.zsku AND nim.total_stock = msm.total_stock AND nim.country_code = msm.country_code
  GROUP BY 1, 2
  UNION ALL
  SELECT core.zsku as sku, nim.country_code, ANY_VALUE(nim.zsku) as instant_sku
  FROM core_zskus core
  JOIN minutes_products nim ON core.nsku = nim.nsku
  JOIN zsku_max_stock_mapping msm ON core.zsku = msm.zsku AND nim.total_stock = msm.total_stock AND nim.country_code = msm.country_code
  GROUP BY 1, 2
  UNION ALL
  SELECT core.zsku as sku, nim.country_code, ANY_VALUE(nim.zsku) as instant_sku
  FROM core_zskus core
  JOIN minutes_products nim USING (partner_barcode, brand_code)
  JOIN zsku_max_stock_mapping msm ON core.zsku = msm.zsku AND nim.total_stock = msm.total_stock AND nim.country_code = msm.country_code
  GROUP BY 1, 2
  UNION ALL
  SELECT core.nsku as sku, nim.country_code, ANY_VALUE(nim.zsku) as instant_sku
  FROM core_nskus core
  JOIN minutes_products nim ON core.zsku = nim.zsku
  JOIN nsku_max_stock_mapping msm ON core.nsku = msm.nsku AND nim.total_stock = msm.total_stock AND nim.country_code = msm.country_code
  GROUP BY 1, 2
  UNION ALL
  SELECT core.nsku as sku, nim.country_code, ANY_VALUE(nim.zsku) as instant_sku
  FROM core_nskus core
  JOIN minutes_products nim ON core.nsku = nim.nsku
  JOIN nsku_max_stock_mapping msm ON core.nsku = msm.nsku AND nim.total_stock = msm.total_stock AND nim.country_code = msm.country_code
  GROUP BY 1, 2
  UNION ALL
  SELECT core.nsku as sku, nim.country_code, ANY_VALUE(nim.zsku) as instant_sku
  FROM core_nskus core
  JOIN minutes_products nim USING (partner_barcode, id_partner, brand_code)
  JOIN nsku_max_stock_mapping msm ON core.nsku = msm.nsku AND nim.total_stock = msm.total_stock AND nim.country_code = msm.country_code
  GROUP BY 1, 2
)
GROUP BY 1, 2