SQLize Online / PHPize Online  /  SQLtest Online

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