SQLize Online / PHPize Online  /  SQLtest Online

create temp table seller_list distkey(merchant_customer_id) as ( select distinct merchant_customer_id,seller_type, seller_channel, am_name from andes.in3pvirt_ddl.uam_merchants where period_end_date>= to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD') and period_start_date<= to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD') and seller_channel in ('SVM', 'CORE') ); create temp table merch AS ( SELECT dmm.merchant_customer_id, dmm.mp_merchant_friendly_name AS merchant_name FROM (SELECT * FROM andes.booker.d_marketplace_merchants WHERE region_id = 4) dmm WHERE dmm.marketplace_id = 44571 ); create temp table ftac_base_cy as ( select gl, asin, merchant_brand_name, merchant_customer_id, merchant_name, seller_channel, am_name, count(FTAC) ftac_sel from ( select rc1.asin, dma.merchant_brand_name, rc1.merchant_id::bigint as merchant_customer_id, merch.merchant_name, seller_list.seller_channel, seller_list.am_name, case when CAM.subcat_art in ('CE_Cameras') then 'gl_camera' when CAM.gl_product_group in (23) then 'gl_electronics' else dma.gl_product_group_desc end as gl, 1 AS FTAC from andes.AIM_BI_DDL.D_DAILY_BBFTI_SOROOS_ASIN_METRIC rc1 INNER JOIN andes.fastrck_ddl.mp_local_country_map cm -- Remove international GVs ON rc1.region_id = cm.region_id AND rc1.marketplace_id = cm.marketplace_id AND rc1.customer_country = cm.local_country_code AND cm.active = 'Y' left join andes.booker.d_mp_asins dma on dma.asin=rc1.asin and dma.marketplace_id=44571 and dma.region_id=4 LEFT JOIN andes.IN_CE_ANALYTICS_DDL.CEPC_ASIN_SUBCAT_MAPPING CAM ON rc1.ASIN = cam.ASIN left join seller_list on seller_list.merchant_customer_id::bigint = rc1.merchant_id::bigint left join andes.in3pvirt_ddl.launchpad_map lp on rc1.merchant_id=lp.merchant_customer_id and lower(lp.merchant_brand_name)=lower(dma.merchant_brand_name) and lp.launch_date<=to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD') and lp.expiry_date>=to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD') left join merch on merch.merchant_customer_id::bigint = rc1.merchant_id::bigint where rc1.marketplace_id = 44571 and rc1.region_id = 4 and rc1.snapshot_day between to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-29 and to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD') and rc1.ft_gv>0 and (seller_list.merchant_customer_id is not null or lp.merchant_customer_id is not null) and rc1.channel_name in ('FBA','MFN') and seller_list.seller_channel in ('SVM', 'CORE') group by 1,2,3,4,5,6,7 ) group by 1,2,3,4,5,6,7 ); create temp table ftac_base_ly as ( select gl, asin, merchant_brand_name, merchant_customer_id, merchant_name, seller_channel, am_name, count(FTAC) ftac_sel from ( select rc1.asin, dma.merchant_brand_name, rc1.merchant_id::bigint as merchant_customer_id, merch.merchant_name, seller_list.seller_channel, seller_list.am_name, case when CAM.subcat_art in ('CE_Cameras') then 'gl_camera' when CAM.gl_product_group in (23) then 'gl_electronics' else dma.gl_product_group_desc end as gl, 1 AS FTAC from andes.AIM_BI_DDL.D_DAILY_BBFTI_SOROOS_ASIN_METRIC rc1 INNER JOIN andes.fastrck_ddl.mp_local_country_map cm -- Remove international GVs ON rc1.region_id = cm.region_id AND rc1.marketplace_id = cm.marketplace_id AND rc1.customer_country = cm.local_country_code AND cm.active = 'Y' left join andes.booker.d_mp_asins dma on dma.asin=rc1.asin and dma.marketplace_id=44571 and dma.region_id=4 LEFT JOIN andes.IN_CE_ANALYTICS_DDL.CEPC_ASIN_SUBCAT_MAPPING CAM ON rc1.ASIN = cam.ASIN left join seller_list on seller_list.merchant_customer_id::bigint = rc1.merchant_id::bigint left join andes.in3pvirt_ddl.launchpad_map lp on rc1.merchant_id=lp.merchant_customer_id and lower(lp.merchant_brand_name)=lower(dma.merchant_brand_name) and lp.launch_date<=to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD') and lp.expiry_date>=to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD') left join merch on merch.merchant_customer_id::bigint = rc1.merchant_id::bigint where rc1.marketplace_id = 44571 and rc1.region_id = 4 and rc1.snapshot_day between (to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-364)-29 and to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-364 and rc1.ft_gv>0 and (seller_list.merchant_customer_id is not null or lp.merchant_customer_id is not null) and rc1.channel_name in ('FBA','MFN') and seller_list.seller_channel in ('SVM', 'CORE') group by 1,2,3,4,5,6,7 ) group by 1,2,3,4,5,6,7 ); create temp table last_year_ftac as (select TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')-364 AS day, EXTRACT(WEEK FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') -364) AS week, EXTRACT(MONTH FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') -364) AS month, EXTRACT(QUARTER FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')-364) AS quarter, EXTRACT(YEAR FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')-364) AS year, gl, asin, merchant_brand_name, merchant_customer_id, merchant_name, seller_channel, am_name, count(ftac_sel) as ftac_ly from ftac_base_ly group by 1,2,3,4,5,6,7,8,9,10,11,12); create temp table current_year_ftac as (select TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') AS day, EXTRACT(WEEK FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')) AS week, EXTRACT(MONTH FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')) AS month, EXTRACT(QUARTER FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')) AS quarter, EXTRACT(YEAR FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')) AS year, gl, asin, merchant_brand_name, merchant_customer_id, merchant_name, seller_channel, am_name, count(ftac_sel) as ftac_cy from ftac_base_cy group by 1,2,3,4,5,6,7,8,9,10,11,12); --------------------- gms --------------------------------------- create temp table base_asin_gms as ( select dma.asin ,case when dma.gl_product_group = 107 and (dma.subcategory_code not in (10700905,10700907) or dma.subcategory_code is null) then 353 else dma.gl_product_group end as gl_product_group ,dma.gl_product_group_desc ,dma.is_deleted ,dma.merchant_brand_name ,case when dma.ASIN in (dmaa.ASIN) then dmaa.cat else dma.CATEGORY_CODE end as category_code ,case when dma.ASIN in (dmaa.ASIN) then dmaa.subcat else dma.subcategory_code end as subcategory_code from andes.booker.d_mp_asins dma LEFT JOIN (select ASIN ,CAST(cat as varchar(10)) AS cat ,CAST(subcat as varchar(10)) AS subcat from ingestion_tool.Other_bucket_dump_ASINS ) dmaa on dma.asin = dmaa.asin where dma.region_id = 4 and dma.marketplace_id = 44571 ); create temp table am_list_gms as ( select * from andes.in3pvirt_ddl.uam_merchants where period_end_date>= TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') and period_start_date<= TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') and am_type<>'ABA' ); create temp table GMS_TOTAL as (select EXTRACT(YEAR FROM ACTIVITY_DAY) AS year, EXTRACT(WEEK FROM ACTIVITY_DAY + 1) AS week, ACTIVITY_DAY, a.asin, b.merchant_brand_name AS brand_name, a.MERCHANT_CUSTOMER_ID, am_list.seller_channel, b.gl_product_group_desc, SUM(served_units) AS served_units, SUM(net_shipped_gms) AS net_shipped_gms from andes.INFPNA.AGG_GL_CHANNEL_TOPLINE a left join (select ASIN , merchant_brand_name,gl_product_group_desc, max(category_code) as category_code , max(subcategory_code) as subcategory_code from base_asin_gms group by 1,2,3) B on A.asin = B.asin left join am_list_gms as am_list on a.MERCHANT_CUSTOMER_ID = am_list.MERCHANT_CUSTOMER_ID LEFT JOIN andes.IN3PVIRT_DDL.D_IN_MERCHANT_CLASSIFICATION dimc ON a.MERCHANT_CUSTOMER_ID = dimc.MERCHANT_CUSTOMER_ID AND dimc.EFF_START_DATE <= TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') AND dimc.EFF_END_DATE >= TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') where a.ACTIVITY_DAY between (TO_DATE('{RUN_DATE_YYYY/MM/DD}', 'YYYY/MM/DD')-364)-29 and TO_DATE('{RUN_DATE_YYYY/MM/DD}', 'YYYY/MM/DD')-364 AND a.MARKETPLACE_ID = 44571 and am_list.seller_channel in ('SVM','CORE') AND a.asin in (select distinct asin from last_year_ftac) group by 1,2,3,4,5,6,7,8); create temp table GMS_loss as (select extract(year from ACTIVITY_DAY) as year, extract(week from ACTIVITY_DAY+1) as week, ACTIVITY_DAY, a.asin, b.merchant_brand_name as brand_name, a.MERCHANT_CUSTOMER_ID, am_list.seller_channel, b.gl_product_group_desc, sum(served_units) as served_units, sum(net_shipped_gms) as net_shipped_gms from andes.INFPNA.AGG_GL_CHANNEL_TOPLINE a left join (select ASIN , merchant_brand_name,gl_product_group_desc, max(category_code) as category_code , max(subcategory_code) as subcategory_code from base_asin_gms group by 1,2,3) B on A.asin = B.asin left join am_list_gms as am_list on a.MERCHANT_CUSTOMER_ID = am_list.MERCHANT_CUSTOMER_ID LEFT JOIN andes.IN3PVIRT_DDL.D_IN_MERCHANT_CLASSIFICATION dimc ON a.MERCHANT_CUSTOMER_ID = dimc.MERCHANT_CUSTOMER_ID AND dimc.EFF_START_DATE <= TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') AND dimc.EFF_END_DATE >= TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') where a.ACTIVITY_DAY between (TO_DATE('{RUN_DATE_YYYY/MM/DD}', 'YYYY/MM/DD')-364)-29 and TO_DATE('{RUN_DATE_YYYY/MM/DD}', 'YYYY/MM/DD')-364 AND a.MARKETPLACE_ID = 44571 and am_list.seller_channel in ('SVM','CORE') AND a.asin in (select distinct asin from last_year_ftac) and a.asin not in (select distinct asin from current_year_ftac) group by 1,2,3,4,5,6,7,8); -------------------------master query ----------------------- create temp table master as (select TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')-364 AS day, EXTRACT(WEEK FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') -364) AS week, EXTRACT(MONTH FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD') -364) AS month, EXTRACT(QUARTER FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')-364) AS quarter, EXTRACT(YEAR FROM TO_DATE('{RUN_DATE_YYYYMMDD}', 'YYYYMMDD')-364) AS year, a.gl, a.asin, replace(a.merchant_brand_name,',',' ') as brand_name, a.merchant_customer_id, replace(a.merchant_name,',',' ') as merchant_name, a.seller_channel, replace(a.am_name,',', ' ') as am_name, COALESCE(count(a.ftac_sel),0) as ftac_ly, COALESCE(sum(b.net_shipped_gms),0) as gms_total, COALESCE(sum(c.net_shipped_gms),0) as gms_loss, SUM(COALESCE(b.net_shipped_gms, 0)) OVER (PARTITION BY gl) AS total_sales from ftac_base_ly a left join GMS_TOTAL b on a.asin = b.asin and a.gl = b.gl_product_group_desc left join GMS_loss c on c.asin = a.asin group by 1,2,3,4,5,6,7,8,9,10,11,12 ); unload ($$ SELECT * FROM master $$) to 's3://yoda-qs-teams/ftac_current_week/master_copy_{RUN_DATE_YYYYMMDD}' iam_role 'arn:aws:iam::840785816044:role/RedshiftCopyUnload2,arn:aws:iam::691147410654:role/RedShiftToS3' delimiter ',' allowoverwrite escape parallel off REGION 'ap-south-1' HEADER; select pg_last_unload_count();