SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
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();
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear