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
select SupplierID , supplier_name , ContractSupplyID , SellerID , seller_name , priority_BU , priority_cat1_bu , priority_cat1 , Le_Regionale , CBSeller , TakeRate_low , AdRev , Regional_Surcharge_w_VAT , OzonAccount , Ozon_Account_Coupons , OtherDiscounts , GP_low_do_skidok , MarketplaceSellerPricewVAT , GMV_MPSP_3d , GP_low_posle_skidok_new , GP_low_posle_skidok_7d , GP_low_posle_skidok_3d , GP_vertica_last Ratio_MPSP_3d, GP_new, round(sum(GP_low_do_skidok_koef_0/nullifzero(MarketplaceSellerPricewVAT) * 100, 2)) as GP_new_Adrev, GP_7d, round(sum(GP_low_do_skidok_koef_2/nullifzero(MarketplaceSellerPricewVAT) * 100, 2)) as GP_7d_Adrev, GP_3d, round(sum(GP_low_do_skidok_koef_3/nullifzero(MarketplaceSellerPricewVAT) * 100, 2)) as GP_3d_Adrev from (select a.SupplierID , s.name supplier_name , a.ContractSupplyID , a.SellerID , nm.Name seller_name , priority_BU , priority_cat1_bu , priority_cat1 , i.Le_Regionale , a.CBSeller , a.TakeRate_low , a.AdRev , a.Regional_Surcharge_w_VAT , a.OzonAccount , a.Ozon_Account_Coupons , a.OtherDiscounts , a.GP_low_do_skidok , a.MarketplaceSellerPricewVAT , GMV_MPSP_3d , a.GP_low_posle_skidok GP_low_posle_skidok_new , a2.GP_low_posle_skidok GP_low_posle_skidok_7d , a3.GP_low_posle_skidok GP_low_posle_skidok_3d , lim.border as Limit , c.Take_rate GP_vertica_last , nullifzero(round(Ratio_MPSP_3d * 100, 2)) as Ratio_MPSP_3d , round((a.GP_low_do_skidok / nullifzero(a.MarketplaceSellerPricewVAT)) * 100, 2) as GP_new, sum((nullifzero(zeroifnull(a.TakeRate_low) + zeroifnull(a.Regional_Surcharge_w_VAT)+ zeroifnull (a.AdRev_koef)))) as GP_low_do_skidok_koef_0 , round((a2.GP_low_do_skidok / nullifzero(a2.MarketplaceSellerPricewVAT)) * 100, 2) as GP_7d, sum((nullifzero(zeroifnull(a2.TakeRate_low) + zeroifnull(a2.Regional_Surcharge_w_VAT)+ zeroifnull (a2.AdRev_koef)))) GP_low_do_skidok_koef_2, round((a3.GP_low_do_skidok / nullifzero(a3.MarketplaceSellerPricewVAT)) * 100, 2) as GP_3d, sum((nullifzero(zeroifnull(a3.TakeRate_low) + zeroifnull(a3.Regional_Surcharge_w_VAT)+ zeroifnull (a3.AdRev_koef)))) GP_low_do_skidok_koef_3 from (select SupplierID , ContractSupplyID , SellerID , CBSeller , sum(TakeRate_low) TakeRate_low , sum(AdRev) AdRev , sum(Regional_Surcharge_w_VAT) Regional_Surcharge_w_VAT , sum(OzonAccount) OzonAccount , sum(Ozon_Account_Coupons) Ozon_Account_Coupons , sum(OtherDiscounts) OtherDiscounts , sum(GP_low_do_skidok) GP_low_do_skidok , sum(GMVwoVAT) GMVwoVAT , sum(GMVwVAT) GMVwVAT , sum(MarketplaceSellerPricewVAT) MarketplaceSellerPricewVAT , sum(GP_low_posle_skidok) GP_low_posle_skidok , sum(MarketplaceSellerPricewoVAT) MarketplaceSellerPricewoVAT , sum(Qty) Qty, sum(case when zeroifnull(AdRev)/nullifzero(MarketplaceSellerPricewVAT) > 0.15 then AdRev * 0.15 else AdRev end) as AdRev_koef from DP_team.GP_low_inputs_by_sellers_by_date q join Dt_now w on cast(q.date as date) = w.date group by 1, 2, 3, 4) a left join (select SellerID , sum(GP_low_do_skidok) GP_low_do_skidok , sum(MarketplaceSellerPricewVAT) MarketplaceSellerPricewVAT , sum(GP_low_posle_skidok) GP_low_posle_skidok , sum(TakeRate_low) TakeRate_low , sum(AdRev) AdRev , sum(Regional_Surcharge_w_VAT) Regional_Surcharge_w_VAT, sum(case when zeroifnull(AdRev)/nullifzero(MarketplaceSellerPricewVAT) > 0.15 then AdRev * 0.15 else AdRev end) as AdRev_koef from DP_team.GP_low_inputs_by_sellers_by_date where cast(date as date) between current_date - 8 and current_date - 2 group by 1) a2 on a.SellerID = a2.SellerID left join (select SellerID , sum(GP_low_do_skidok) GP_low_do_skidok , sum(MarketplaceSellerPricewVAT) MarketplaceSellerPricewVAT , sum(GP_low_posle_skidok) GP_low_posle_skidok , sum(TakeRate_low) TakeRate_low , sum(AdRev) AdRev , sum(Regional_Surcharge_w_VAT) Regional_Surcharge_w_VAT, sum(case when zeroifnull(AdRev)/nullifzero(MarketplaceSellerPricewVAT) > 0.15 then AdRev * 0.15 else AdRev end) as AdRev_koef from DP_team.GP_low_inputs_by_sellers_by_date where cast(date as date) between current_date - 4 and current_date - 2 group by 1) a3 on a.SellerID = a3.SellerID left join (select max(date) as date from ozoncard_team.GP_control_sellerid) z on 1 = 1 left join ozoncard_team.GP_control_sellerid c --GP_vertica_last on a.sellerid = c.sellerid and c.date = z.date --and c.date = '2024-05-13' left join ozoncard_team.limit_sellers_GP lim on a.SellerID = lim.sellerid left join metazonbeeeye.Supplier s on a.SupplierID = s.id left join dwh_data.Anc_Seller as ans on ans.sourcekey = a.SellerID left join dwh_data.Atr_Seller_Name nm on nm.SellerId = ans.SellerId left join (select distinct SellerID, Le_Regionale from bpanalytics_team.item_wide_dwhID) i on a.SellerID = i.SellerID left join (select SellerID , sum(GMV_MPSP) GMV_MPSP_3d , sum(nullifzero(zeroifnull(total_spendings_oa) + zeroifnull(total_spendings_dd))) / sum(GMV_MPSP) Ratio_MPSP_3d from bpanalytics_team.item_wide_dwhID iw left join ozoncard_team.marketing_spendings_by_sku_daily s on iw.RezonItemID = s.RezonItemID and factdate between current_date - 3 and current_date - 1 group by 1) sp on a.SellerID = sp.SellerID left join (select SellerID , business_unit priority_BU , GMV_MPSP , row_number() over (partition by sellerid order by GMV_MPSP desc nulls last) as rn from (select SellerID , business_unit , sum(GMV_MPSP) GMV_MPSP from bpanalytics_team.item_wide_dwhID iw join ozoncard_team.marketing_spendings_by_sku_daily s on iw.RezonItemID = s.RezonItemID and factdate between current_date - 30 and current_date - 1 group by 1, 2) q) spb on a.SellerID = spb.SellerID and spb.rn = 1 left join (select SellerID , business_unit priority_cat1_bu , business_unit_level1 priority_cat1 , GMV_MPSP , row_number() over (partition by sellerid order by GMV_MPSP desc nulls last) as rn from (select SellerID , business_unit , business_unit_level1 , sum(GMV_MPSP) GMV_MPSP from bpanalytics_team.item_wide_dwhID iw join ozoncard_team.marketing_spendings_by_sku_daily s on iw.RezonItemID = s.RezonItemID and factdate between current_date - 30 and current_date - 1 group by 1, 2, 3) q) spb2 on a.SellerID = spb2.SellerID and spb2.rn = 1 group by 1, 2 ,3 ,4 ,5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,18,19,20,21,22,23,24,25,26, 28,30 order by GP_low_posle_skidok_new) e
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