Created
May 29, 2024 14:47
-
-
Save meetchandan/bb2d3c31f82e107ba1dacbba5c25647c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment