--------------------------Order Level Data -------------------------- Drop table if exists raena_analytics.Gm_final_table; CREATE TABLE raena_analytics.Gm_final_table AS SELECT A.external_id, cast(transaction_date as date) transaction_date , A.order_discount_amount, A.shipping_cost, A.shipping_province, A.coupon_code, A.discount_type, A.coupon_applied_on, A.brand_name, B.product_type, A.category_name, B.name sku_name, A.reseller_name, A.reseller_email, A.reseller_mobile, A.tier_name , A.reseller_id, A.sku, A.quantity, A.retail_price, A.seller_margin, A.discounted_price, A.additional_discount, A.item_type , A.cogs FROM raena_analytics.gm_dashboard A LEFT JOIN raena_catalog_management.product B ON A.sku = B.sku; Drop table if exists raena_analytics.final_order_level_data; CREATE TABLE raena_analytics.final_order_level_data AS SELECT A.*, CURRENT_DATE, date_part('Month',CURRENT_DATE) month_of_date, date_part('Week',CURRENT_DATE) Week_of_date, date_part('Day',CURRENT_DATE) Day_of_date, retail_price*quantity AS revenue_on_retail, (retail_price*quantity)-(seller_margin*quantity) AS revenue_on_wholesale, (discounted_price*quantity) AS revenue_on_after_discount, (cogs*quantity) AS total_cogs, case when retail_price<>0 then cast(((cast(((retail_price*quantity)-(seller_margin*quantity)) AS decimal(22,2))-(cast(cogs*quantity AS decimal(22,2))))/cast(((retail_price*quantity)-(seller_margin*quantity)) AS decimal(22,2)))*100 AS decimal(22,2)) end pre_discount_gm, case when discounted_price<>0 then cast(((cast((discounted_price*quantity) AS decimal(22,2))-(cast(cogs*quantity AS decimal(22,2))))/cast(((discounted_price*quantity)) AS decimal(22,2)))*100 AS decimal(22,2)) end post_discount_gm, shipping_city, shipping_pincode, shipping_country_code, order_warehouse FROM raena_analytics.Gm_final_table A LEFT JOIN (SELECT id , shipping_city, shipping_pincode, shipping_country_code, order_warehouse FROM raena_order_management.order WHERE cast(created_at AS date) >=dateadd(d,-120,'2024-06-23')) B ON external_id =id WHERE discounted_price <>0 and cast(transaction_date AS date) between dateadd(d,-120,'2024-06-23') and '2024-06-23'; -------------------------------------------SKU Level Data --------------------------------- DROP TABLE IF EXISTS raena_analytics.sku_warehouse_split_stage1; CREATE TABLE raena_analytics.sku_warehouse_split_stage1 AS SELECT sku , CASE WHEN shipping_province IN ('Kalimantan Timur', 'Kalimantan Utara', 'Kalimantan Selatan', 'Kalimantan Tengah', 'Kalimantan Barat') THEN 'Balikpapan - Semarandi' WHEN shipping_province IN ('DKI Jakarta', 'Banten', 'Jawa Barat') THEN 'Bekasi' WHEN shipping_province IN ('Sulawesi Selatan', 'Sulawesi Tengah', 'Gorontalo', 'Papua', 'Maluku Utara', 'Sulawesi Utara', 'Maluku', 'Sulawesi Tenggara', 'Papua Barat', 'Sulawesi Barat') THEN 'Makassar' WHEN shipping_province IN ('Sumatera Utara', 'Sumatera Selatan', 'Riau', 'Sumatera Barat', 'Lampung', 'Aceh', 'Bangka Belitung', 'Jambi', 'Nanggroe Aceh Darussalam (NAD)', 'Kepulauan Riau', 'Bengkulu') THEN 'Pekanbaru - Medan' WHEN shipping_province IN ('Jawa Tengah', 'DI Yogyakarta') THEN 'Semarang' WHEN shipping_province IN ('Jawa Timur', 'Bali', 'Nusa Tenggara Timur (NTT)', 'Nusa Tenggara Timur', 'Nusa Tenggara Barat', 'Nusa Tenggara Barat (NTB)') THEN 'Surabaya' END warehouse, CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2024-06-23') AND '2024-06-23' THEN 'L7D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2024-06-23') AND dateadd(d,-7,'2024-06-23') THEN 'L14D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2024-06-23') AND dateadd(d,-14,'2024-06-23') THEN 'L21D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2024-06-23') AND dateadd(d,-21,'2024-06-23') THEN 'L28D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2024-06-23') AND dateadd(d,-28,'2024-06-23') THEN 'L35D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2024-06-23') AND dateadd(d,-35,'2024-06-23') THEN 'L42D' END Bucket, Total_revenue, sum(revenue_on_after_discount) revenue_on_after_discount, sum(revenue_on_after_discount)*100/Total_revenue AS contribution FROM raena_analytics.final_order_level_data A LEFT JOIN (SELECT CASE WHEN shipping_province IN ('Kalimantan Timur', 'Kalimantan Utara', 'Kalimantan Selatan', 'Kalimantan Tengah', 'Kalimantan Barat') THEN 'Balikpapan - Semarandi' WHEN shipping_province IN ('DKI Jakarta', 'Banten', 'Jawa Barat') THEN 'Bekasi' WHEN shipping_province IN ('Sulawesi Selatan', 'Sulawesi Tengah', 'Gorontalo', 'Papua', 'Maluku Utara', 'Sulawesi Utara', 'Maluku', 'Sulawesi Tenggara', 'Papua Barat', 'Sulawesi Barat') THEN 'Makassar' WHEN shipping_province IN ('Sumatera Utara', 'Sumatera Selatan', 'Riau', 'Sumatera Barat', 'Lampung', 'Aceh', 'Bangka Belitung', 'Jambi', 'Nanggroe Aceh Darussalam (NAD)', 'Kepulauan Riau', 'Bengkulu') THEN 'Pekanbaru - Medan' WHEN shipping_province IN ('Jawa Tengah', 'DI Yogyakarta') THEN 'Semarang' WHEN shipping_province IN ('Jawa Timur', 'Bali', 'Nusa Tenggara Timur (NTT)', 'Nusa Tenggara Timur', 'Nusa Tenggara Barat', 'Nusa Tenggara Barat (NTB)') THEN 'Surabaya' END warehouse, CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2024-06-23') AND '2024-06-23' THEN 'L7D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2024-06-23') AND dateadd(d,-7,'2024-06-23') THEN 'L14D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2024-06-23') AND dateadd(d,-14,'2024-06-23') THEN 'L21D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2024-06-23') AND dateadd(d,-21,'2024-06-23') THEN 'L28D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2024-06-23') AND dateadd(d,-28,'2024-06-23') THEN 'L35D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2024-06-23') AND dateadd(d,-35,'2024-06-23') THEN 'L42D' END Bucket, sum(coalesce (revenue_on_after_discount,0)) Total_revenue FROM raena_analytics.final_order_level_data WHERE cast(transaction_date as date) BETWEEN dateadd(d,-41,'2024-06-23') AND '2024-06-23' GROUP BY CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2024-06-23') AND '2024-06-23' THEN 'L7D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2024-06-23') AND dateadd(d,-7,'2024-06-23') THEN 'L14D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2024-06-23') AND dateadd(d,-14,'2024-06-23') THEN 'L21D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2024-06-23') AND dateadd(d,-21,'2024-06-23') THEN 'L28D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2024-06-23') AND dateadd(d,-28,'2024-06-23') THEN 'L35D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2024-06-23') AND dateadd(d,-35,'2024-06-23') THEN 'L42D' END, CASE WHEN shipping_province IN ('Kalimantan Timur', 'Kalimantan Utara', 'Kalimantan Selatan', 'Kalimantan Tengah', 'Kalimantan Barat') THEN 'Balikpapan - Semarandi' WHEN shipping_province IN ('DKI Jakarta', 'Banten', 'Jawa Barat') THEN 'Bekasi' WHEN shipping_province IN ('Sulawesi Selatan', 'Sulawesi Tengah', 'Gorontalo', 'Papua', 'Maluku Utara', 'Sulawesi Utara', 'Maluku', 'Sulawesi Tenggara', 'Papua Barat', 'Sulawesi Barat') THEN 'Makassar' WHEN shipping_province IN ('Sumatera Utara', 'Sumatera Selatan', 'Riau', 'Sumatera Barat', 'Lampung', 'Aceh', 'Bangka Belitung', 'Jambi', 'Nanggroe Aceh Darussalam (NAD)', 'Kepulauan Riau', 'Bengkulu') THEN 'Pekanbaru - Medan' WHEN shipping_province IN ('Jawa Tengah', 'DI Yogyakarta') THEN 'Semarang' WHEN shipping_province IN ('Jawa Timur', 'Bali', 'Nusa Tenggara Timur (NTT)', 'Nusa Tenggara Timur', 'Nusa Tenggara Barat', 'Nusa Tenggara Barat (NTB)') THEN 'Surabaya' END) B ON CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2024-06-23') AND '2024-06-23' THEN 'L7D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2024-06-23') AND dateadd(d,-7,'2024-06-23') THEN 'L14D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2024-06-23') AND dateadd(d,-14,'2024-06-23') THEN 'L21D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2024-06-23') AND dateadd(d,-21,'2024-06-23') THEN 'L28D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2024-06-23') AND dateadd(d,-28,'2024-06-23') THEN 'L35D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2024-06-23') AND dateadd(d,-35,'2024-06-23') THEN 'L42D' END=B.bucket AND CASE WHEN shipping_province IN ('Kalimantan Timur', 'Kalimantan Utara', 'Kalimantan Selatan', 'Kalimantan Tengah', 'Kalimantan Barat') THEN 'Balikpapan - Semarandi' WHEN shipping_province IN ('DKI Jakarta', 'Banten', 'Jawa Barat') THEN 'Bekasi' WHEN shipping_province IN ('Sulawesi Selatan', 'Sulawesi Tengah', 'Gorontalo', 'Papua', 'Maluku Utara', 'Sulawesi Utara', 'Maluku', 'Sulawesi Tenggara', 'Papua Barat', 'Sulawesi Barat') THEN 'Makassar' WHEN shipping_province IN ('Sumatera Utara', 'Sumatera Selatan', 'Riau', 'Sumatera Barat', 'Lampung', 'Aceh', 'Bangka Belitung', 'Jambi', 'Nanggroe Aceh Darussalam (NAD)', 'Kepulauan Riau', 'Bengkulu') THEN 'Pekanbaru - Medan' WHEN shipping_province IN ('Jawa Tengah', 'DI Yogyakarta') THEN 'Semarang' WHEN shipping_province IN ('Jawa Timur', 'Bali', 'Nusa Tenggara Timur (NTT)', 'Nusa Tenggara Timur', 'Nusa Tenggara Barat', 'Nusa Tenggara Barat (NTB)') THEN 'Surabaya' END=warehouse WHERE cast(transaction_date as date) BETWEEN dateadd(d,-41,'2024-06-23') AND '2024-06-23' GROUP BY sku , CASE WHEN shipping_province IN ('Kalimantan Timur', 'Kalimantan Utara', 'Kalimantan Selatan', 'Kalimantan Tengah', 'Kalimantan Barat') THEN 'Balikpapan - Semarandi' WHEN shipping_province IN ('DKI Jakarta', 'Banten', 'Jawa Barat') THEN 'Bekasi' WHEN shipping_province IN ('Sulawesi Selatan', 'Sulawesi Tengah', 'Gorontalo', 'Papua', 'Maluku Utara', 'Sulawesi Utara', 'Maluku', 'Sulawesi Tenggara', 'Papua Barat', 'Sulawesi Barat') THEN 'Makassar' WHEN shipping_province IN ('Sumatera Utara', 'Sumatera Selatan', 'Riau', 'Sumatera Barat', 'Lampung', 'Aceh', 'Bangka Belitung', 'Jambi', 'Nanggroe Aceh Darussalam (NAD)', 'Kepulauan Riau', 'Bengkulu') THEN 'Pekanbaru - Medan' WHEN shipping_province IN ('Jawa Tengah', 'DI Yogyakarta') THEN 'Semarang' WHEN shipping_province IN ('Jawa Timur', 'Bali', 'Nusa Tenggara Timur (NTT)', 'Nusa Tenggara Timur', 'Nusa Tenggara Barat', 'Nusa Tenggara Barat (NTB)') THEN 'Surabaya' END, CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2024-06-23') AND '2024-06-23' THEN 'L7D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2024-06-23') AND dateadd(d,-7,'2024-06-23') THEN 'L14D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2024-06-23') AND dateadd(d,-14,'2024-06-23') THEN 'L21D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2024-06-23') AND dateadd(d,-21,'2024-06-23') THEN 'L28D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2024-06-23') AND dateadd(d,-28,'2024-06-23') THEN 'L35D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2024-06-23') AND dateadd(d,-35,'2024-06-23') THEN 'L42D' END , Total_revenue; DROP TABLE IF EXISTS raena_analytics.sku_warehouse_split_stage2; CREATE TABLE raena_analytics.sku_warehouse_split_stage2 AS SELECT sku , warehouse , sum(CASE WHEN bucket = 'L7D' THEN contribution END) L7D_sku_contribution, sum(CASE WHEN bucket = 'L14D' THEN contribution END) L14D_sku_contribution, sum(CASE WHEN bucket = 'L21D' THEN contribution END) L21D_sku_contribution, sum(CASE WHEN bucket = 'L28D' THEN contribution END) L28D_sku_contribution, sum(CASE WHEN bucket = 'L35D' THEN contribution END) L35D_sku_contribution, sum(CASE WHEN bucket = 'L42D' THEN contribution END) L42D_sku_contribution, percentile_cont(0.5) within GROUP ( ORDER BY contribution) median_contribution, percentile_cont(0.95) within GROUP ( ORDER BY contribution) Percentile95_contribution, coalesce(stddev_samp(contribution),sum(contribution)) sd_contribution FROM raena_analytics.sku_warehouse_split_stage1 WHERE warehouse IS NOT NULL GROUP BY sku, warehouse ; DROP TABLE IF EXISTS raena_analytics.sku_warehouse_split_stage3; CREATE TABLE raena_analytics.sku_warehouse_split_stage3 AS SELECT * , (CASE WHEN sd_contribution <= 0.1 THEN median_contribution ELSE Percentile95_contribution END)sku_qty_split_next_order FROM raena_analytics.sku_warehouse_split_stage2; DROP TABLE IF EXISTS raena_analytics.sku_warehouse_split_final; CREATE TABLE raena_analytics.sku_warehouse_split_final AS SELECT sku, warehouse, l7d_sku_contribution, L14D_sku_contribution, L21D_sku_contribution, L28D_sku_contribution, L35D_sku_contribution, L42D_sku_contribution, median_contribution, Percentile95_contribution, sd_contribution, case when sum(sku_qty_split_next_order)over(partition BY sku)= 0 then 0 else sku_qty_split_next_order*100/sum(sku_qty_split_next_order)over(partition BY sku) end sku_qty_split_next_order_percentage FROM raena_analytics.sku_warehouse_split_stage3; --------------------------------------Warehouse SKu Split---------------------------------- select count(1)sku_final_split_warehouse from raena_analytics.sku_warehouse_split_final; DROP TABLE IF EXISTS raena_analytics.sku_level_data ; CREATE TABLE raena_analytics.sku_level_data AS SELECT A.sku, A.name sku_name, A.is_private, A.country, CASE WHEN A.is_archived = 'true' THEN 'Permanatly_delisted' WHEN A.is_delisted = 'true' THEN 'delisted' WHEN A.is_archived = 'false' AND A.is_delisted = 'false' THEN 'Active' END active_status, A.created_at, B.storage_location storage_type, A.origin sourcing, case when C.business_type like '%Consignment%' then 'Consignment' else 'Outright' end ordering_type , D.name brand_name , A.product_type , E.name category_name, F.cogs , CASE WHEN A.origin= 'Local' THEN coalesce(G.bekasi_buffer_time,2) WHEN A.origin = 'Crossborder' THEN 15 END Bekasi_buffer_lead_time, CASE WHEN A.origin= 'Local' THEN coalesce(G.bekasi_lead_time,5) WHEN A.origin = 'Crossborder' THEN 50 END bekasi_lead_time, CASE WHEN A.origin= 'Local' THEN (coalesce(G.bekasi_lead_time,5)+coalesce(G.bekasi_buffer_time,2)) WHEN A.origin = 'Crossborder' THEN 65 END bekasi_total_lead_time, CASE WHEN origin= 'Local' THEN 45 WHEN origin = 'Crossborder' THEN 30 END bekasi_inventory_days, CASE WHEN origin= 'Local' THEN coalesce(G.semarinda_buffer_time,2) WHEN origin = 'Crossborder' THEN 15 END Balikpapan_Semarandi_buffer_lead_time, CASE WHEN origin= 'Local' THEN coalesce(G.semarinda_lead_time,12) WHEN origin = 'Crossborder' THEN 50 END Balikpapan_Semarandi_lead_time, CASE WHEN origin= 'Local' THEN (coalesce(G.semarinda_lead_time,12)+coalesce(G.semarinda_buffer_time,2)) WHEN origin = 'Crossborder' THEN 65 END Balikpapan_Semarandi_total_lead_time, CASE WHEN origin= 'Local' THEN 30 WHEN origin = 'Crossborder' THEN 30 END Balikpapan_Semarandi_inventory_days, CASE WHEN origin= 'Local' THEN coalesce(G.pekanbaru_buffer_time,2) WHEN origin = 'Crossborder' THEN 15 END Pekanbaru_Medan_buffer_lead_time, CASE WHEN origin= 'Local' THEN coalesce(G.pekanbaru_lead_time,12) WHEN origin = 'Crossborder' THEN 50 END Pekanbaru_Medan_lead_time, CASE WHEN origin= 'Local' THEN (coalesce(G.pekanbaru_lead_time,12)+coalesce(G.pekanbaru_buffer_time,2)) WHEN origin = 'Crossborder' THEN 65 END Pekanbaru_Medan_total_lead_time, CASE WHEN origin= 'Local' THEN 30 WHEN origin = 'Crossborder' THEN 30 END Pekanbaru_Medan_inventory_days, CASE WHEN origin= 'Local' THEN coalesce(semarang_buffer_time,2) WHEN origin = 'Crossborder' THEN 15 END Semarang_buffer_lead_time, CASE WHEN origin= 'Local' THEN coalesce(semarang_lead_time,7) WHEN origin = 'Crossborder' THEN 50 END Semarang_lead_time, CASE WHEN origin= 'Local' THEN ( coalesce(semarang_lead_time,7)+coalesce(semarang_buffer_time,2)) WHEN origin = 'Crossborder' THEN 65 END Semarang_total_lead_time, CASE WHEN origin= 'Local' THEN 30 WHEN origin = 'Crossborder' THEN 30 END Semarang_inventory_days, CASE WHEN origin= 'Local' THEN coalesce(surabaya_buffer_time,2) WHEN origin = 'Crossborder' THEN 15 END Surabaya_buffer_lead_time, CASE WHEN origin= 'Local' THEN coalesce(surabaya_lead_time,7) WHEN origin = 'Crossborder' THEN 50 END Surabaya_lead_time, CASE WHEN origin= 'Local' THEN coalesce(surabaya_lead_time,7)+coalesce(surabaya_buffer_time,2) WHEN origin = 'Crossborder' THEN 65 END Surabaya_total_lead_time, CASE WHEN origin= 'Local' THEN 30 WHEN origin = 'Crossborder' THEN 30 END Surabaya_inventory_days, CASE WHEN origin= 'Local' THEN coalesce(makassar_buffer_time,2) WHEN origin = 'Crossborder' THEN 15 END Makassar_buffer_lead_time, CASE WHEN origin= 'Local' THEN coalesce(makassar_lead_time,12) WHEN origin = 'Crossborder' THEN 50 END Makassar_lead_time, CASE WHEN origin= 'Local' THEN (coalesce(makassar_lead_time,12)+coalesce(makassar_buffer_time,2)) WHEN origin = 'Crossborder' THEN 65 END Makassar_total_lead_time, CASE WHEN origin= 'Local' THEN 30 WHEN origin = 'Crossborder' THEN 30 END Makassar_inventory_days, multiple_flag, G.moq, moqremark, case when moqremark = 'NOT ORDER GIVE AWAY ITEM' then 'Yes' else 'No' end give_away FROM raena_catalog_management.product A LEFT JOIN raena_analytics.storage_data B ON A.sku=B.sku left join raena_analytics.demand_forecasting_configs G on A.sku= G.sku LEFT JOIN raena_analytics.outright_consignment C ON A.sku = C.sku LEFT JOIN raena_catalog_management.brand D ON A.brand_id = D.id LEFT JOIN raena_catalog_management.category E ON A.category_id =E.id LEFT JOIN (select distinct sku sku_code ,cogs_non_promo cogs from raena_analytics.sku_cogs_audit sca where sku_cogs_action='CREATED') F ON A.sku= F.sku_code ; ---------------------------_basic Calculation ------------------------- DROP TABLE IF EXISTS raena_analytics.basic_calculation; CREATE TABLE raena_analytics.basic_calculation AS SELECT sku , CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2024-06-23') AND '2024-06-23' THEN 'L30D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2024-06-23') AND dateadd(d,-31,'2024-06-23') THEN 'L60D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2024-06-23') AND dateadd(d,-61,'2024-06-23') THEN 'L90D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2024-06-23') AND dateadd(d,-91,'2024-06-23') THEN 'L120D' END txn_period, sum(revenue_on_after_discount) revenue, Total_revenue, sum(revenue_on_after_discount)*100/Total_revenue AS contribution, sum(quantity) quantity, count(DISTINCT transaction_date) days, cast(cast(sum(quantity) as FLOAT)/cast(count(DISTINCT cast(transaction_date as date) ) as FLOAT) as FLOAT) runrate FROM raena_analytics.final_order_level_data A LEFT JOIN (SELECT CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2024-06-23') AND '2024-06-23' THEN 'L30D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2024-06-23') AND dateadd(d,-31,'2024-06-23') THEN 'L60D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2024-06-23') AND dateadd(d,-61,'2024-06-23') THEN 'L90D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2024-06-23') AND dateadd(d,-91,'2024-06-23') THEN 'L120D' END txn_period, sum(coalesce (revenue_on_after_discount,0)) Total_revenue FROM raena_analytics.final_order_level_data WHERE cast(transaction_date as date) BETWEEN dateadd(d,-120,'2024-06-23') AND '2024-06-23' GROUP BY CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2024-06-23') AND '2024-06-23' THEN 'L30D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2024-06-23') AND dateadd(d,-31,'2024-06-23') THEN 'L60D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2024-06-23') AND dateadd(d,-61,'2024-06-23') THEN 'L90D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2024-06-23') AND dateadd(d,-91,'2024-06-23') THEN 'L120D' END) B ON (CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2024-06-23') AND '2024-06-23' THEN 'L30D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2024-06-23') AND dateadd(d,-31,'2024-06-23') THEN 'L60D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2024-06-23') AND dateadd(d,-61,'2024-06-23') THEN 'L90D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2024-06-23') AND dateadd(d,-91,'2024-06-23') THEN 'L120D' END)=B.txn_period WHERE cast(transaction_date as date) BETWEEN dateadd(d,-120,'2024-06-23') AND '2024-06-23' GROUP BY sku, CASE WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2024-06-23') AND '2024-06-23' THEN 'L30D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2024-06-23') AND dateadd(d,-31,'2024-06-23') THEN 'L60D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2024-06-23') AND dateadd(d,-61,'2024-06-23') THEN 'L90D' WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2024-06-23') AND dateadd(d,-91,'2024-06-23') THEN 'L120D' END, Total_revenue; select 1; DROP TABLE IF EXISTS raena_analytics.basic_calculation_stage1; CREATE TABLE raena_analytics.basic_calculation_stage1 AS SELECT A.sku , gold_price , CASE WHEN L120D_revenue = 0 AND L90D_revenue = 0 AND L60D_revenue = 0 THEN 0 ELSE ((CASE WHEN L120D_revenue = 0 THEN 0 ELSE ((L90D_revenue -L120D_revenue)*100/L120D_revenue) END) + (CASE WHEN L90D_revenue= 0 THEN 0 ELSE ((L60D_revenue -L90D_revenue)*100/L90D_revenue) END) +(CASE WHEN L60D_revenue = 0 THEN 0 ELSE ((L30D_revenue -L60D_revenue)*100/L60D_revenue)END)) / (CASE WHEN L120D_revenue = 0 THEN 0 ELSE 1 END + CASE WHEN L90D_revenue = 0 THEN 0 ELSE 1 END+CASE WHEN L60D_revenue = 0 THEN 0 ELSE 1 END) END AVG_revenue_growth FROM (SELECT sku , SUM(CASE WHEN txn_period ='L120D' THEN coalesce(revenue,0) ELSE 0 END) L120D_revenue, SUM(CASE WHEN txn_period ='L90D' THEN coalesce(revenue,0) ELSE 0 END) L90D_revenue, sum(CASE WHEN txn_period ='L60D' THEN coalesce(revenue,0) ELSE 0 END) L60D_revenue, sum(CASE WHEN txn_period ='L30D' THEN coalesce(revenue,0) ELSE 0 END) L30D_revenue FROM raena_analytics.basic_calculation GROUP BY sku) A LEFT JOIN (SELECT sku , json_extract_path_text(tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) gold_price FROM raena_catalog_management.product p) B ON A.sku = B.sku; Drop table if exists raena_analytics.basic_calculation_stage1_v1; create table raena_analytics.basic_calculation_stage1_v1 as select A.*, abs(contribution-median_contribution) for_max_contribution from raena_analytics.basic_calculation A left join (select sku , MEDIAN(contribution) median_contribution from raena_analytics.basic_calculation group by sku ) B on A.sku = B.sku ; DROP TABLE IF EXISTS raena_analytics.basic_calculation_stage2; CREATE TABLE raena_analytics.basic_calculation_stage2 AS SELECT A.SKu , gold_price, sum(CASE WHEN txn_period ='L30D' THEN coalesce(revenue,0) ELSE 0 END) L30D_revenue , B.AVG_revenue_growth , 10 AS avg_expected_revenue_growth, sum(CASE WHEN txn_period ='L30D' THEN coalesce(revenue,0) ELSE 0 END)*(1.10) expected_30D_revenue, MEDIAN(contribution) median_of_contribution, coalesce(stddev_samp(contribution),sum(contribution)) sd_of_contribution, max(contribution) max_contribution , max(for_max_contribution) for_max_contribution, avg(quantity) avg_quantity, max(quantity) max_quantity, case when L90D_revenue= 0 and L60D_revenue= 0 and L30D_revenue >0 then 'Yes' else 'No' end New_sku FROM raena_analytics.basic_calculation_stage1_v1 A LEFT JOIN raena_analytics.basic_calculation_stage1 B ON A.sku=B.sku left join (select sku , coalesce(sum(case when txn_period ='L30D' then coalesce(revenue,0) end),0) as l30D_revenue, coalesce(sum(case when txn_period ='L60D' then coalesce(revenue,0) end),0) as l60D_revenue, coalesce(sum(case when txn_period ='L90D' then coalesce(revenue,0) end),0) as l90D_revenue from raena_analytics.basic_calculation_stage1_v1 where txn_period <> 'L120D' group by sku) C On A.sku = C.sku WHERE txn_period <> 'L120D' GROUP BY A.SKu, B.AVG_revenue_growth, gold_price, case when L90D_revenue= 0 and L60D_revenue= 0 and L30D_revenue >0 then 'Yes' else 'No' end; --SELECT *FROM raena_analytics.basic_calculation_stage2 DROP TABLE IF EXISTS raena_analytics.basic_calculation_stage3; /* CREATE TABLE raena_analytics.basic_calculation_stage3 AS SELECT A.SKu, percentile_cont(0.5) within GROUP ( ORDER BY runrate) median_runrate, max(runrate) max_runrate, sum(CASE WHEN txn_period ='L30D' THEN quantity/days END)last_month_runrate, CASE WHEN --max(contribution)>0.02 or txn_period ='L30D' and max(runrate)>= 30 THEN 'Fast Moving' WHEN --max(contribution)>0.01 or txn_period ='L30D' and max(runrate) between 5 and 29 THEN 'Slow Moving' WHEN --max(contribution)<=0.01 or txn_period ='L30D' and max(runrate)<5 THEN 'Not Moving' END sku_type FROM raena_analytics.basic_calculation A WHERE txn_period <> 'L120D' GROUP BY A.SKu,txn_period; */ CREATE TABLE raena_analytics.basic_calculation_stage3 AS SELECT A.SKu, percentile_cont(0.5) within GROUP ( ORDER BY A.runrate) median_runrate, max(A.runrate) max_runrate, sum(CASE WHEN txn_period ='L30D' THEN quantity/days END)last_month_runrate, CASE WHEN --max(contribution)>0.02 or max(B.runrate)> 30 THEN 'Fast Moving' WHEN --max(contribution)>0.01 or max(B.runrate)>= 5 THEN 'Slow Moving' WHEN --max(contribution)<=0.01 or max(B.runrate)<5 or max(B.runrate) is null THEN 'Not Moving' END sku_type FROM raena_analytics.basic_calculation A left join (select distinct sku , runrate from raena_analytics.basic_calculation where txn_period = 'L30D' ) B on A.sku = B.sku WHERE txn_period <> 'L120D' GROUP BY A.SKu; DROP TABLE IF EXISTS raena_analytics.basic_calculation_stage4; CREATE TABLE raena_analytics.basic_calculation_stage4 AS SELECT A.* , CASE WHEN sd_of_contribution= 0 THEN 0 ELSE (A.for_max_contribution)/sd_of_contribution END AS max_deviation_contribution, B.median_runrate, B.max_runrate, coalesce(B.last_month_runrate,0)last_month_runrate , B.sku_type, (l30d_revenue/gold_price)*(1+(avg_revenue_growth/100)) AS qty1_median_rev_contribution, median_runrate*30 qty2_avg_runrate, avg_quantity qty3_avg_quantity, expected_30D_revenue/gold_price qty4_expected_revenue_target, 0 buffer FROM raena_analytics.basic_calculation_stage2 A LEFT JOIN raena_analytics.basic_calculation_stage3 B ON A.sku = B.sku; --SELECT * FROM raena_analytics.basic_calculation_stage4; --select * from raena_analytics.basic_calculation_stage5 drop table if exists raena_analytics.basic_calculation_stage5; CREATE TABLE raena_analytics.basic_calculation_stage5 as select sku , MEDIAN(Max_of_qty) Max_of_qty , Avg(Max_of_qty) Avg_of_quantiy from (SELECT sku , qty1_median_rev_contribution Max_of_qty FROM raena_analytics.basic_calculation_stage4 UNION ALL SELECT sku , qty2_avg_runrate FROM raena_analytics.basic_calculation_stage4 UNION ALL SELECT sku , qty3_avg_quantity FROM raena_analytics.basic_calculation_stage4 UNION ALL SELECT sku , qty4_expected_revenue_target FROM raena_analytics.basic_calculation_stage4) A group by sku; drop table if exists raena_analytics.basic_calculation_stage6; create table raena_analytics.basic_calculation_stage6 as select sku, sum(CASE WHEN B.warehouse ='Balikpapan - Semarandi' THEN sku_qty_split_next_order_percentage/100 END) balikpapan_semarandi_split, sum(CASE WHEN B.warehouse ='Bekasi' THEN sku_qty_split_next_order_percentage/100 END) Bekasi_split, sum(CASE WHEN B.warehouse ='Makassar' THEN sku_qty_split_next_order_percentage/100 END) Makassar_split, sum(CASE WHEN B.warehouse ='Pekanbaru - Medan' THEN sku_qty_split_next_order_percentage/100 END) pekanbaru_medan_split, sum(CASE WHEN B.warehouse ='Semarang' THEN sku_qty_split_next_order_percentage/100 END) Semarang_split, sum(CASE WHEN B.warehouse ='Surabaya' THEN sku_qty_split_next_order_percentage/100 END) Surabaya_split from raena_analytics.sku_warehouse_split_final B group by sku; drop table if exists raena_analytics.final_basic_calucation_table; CREATE TABLE raena_analytics.final_basic_calucation_table AS SELECT A.sku , A.gold_price , A.l30d_revenue , A.avg_revenue_growth, A.avg_expected_revenue_growth , A.expected_30D_revenue, A.median_of_contribution median_of_contribution_l90D, A.sd_of_contribution standard_deviation_of_contribution_l90d, A.max_deviation_contribution max_standard_deviation_of_contribution_l90d, A.avg_quantity avg_quantity_sold_l90d, A.max_quantity max_quantity_sold_l90d, A.median_runrate median_runrate_l90d, A.max_runrate max_runrate_l90d, A.last_month_runrate l30d_runrate, A.sku_type , A.new_sku as new_sku_flag, A.qty1_median_rev_contribution , A.qty2_avg_runrate , A.qty3_avg_quantity , qty4_expected_revenue_target, buffer , (case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30) when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30) when sku_type = 'Not Moving' then 0 end) overall_qty_required_per_day, (case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30) when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30) when sku_type = 'Not Moving' then 0 end )*balikpapan_semarandi_split balikpapan_semarandi_split, (case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30) when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30) when sku_type = 'Not Moving' then 0 end)*Bekasi_split Bekasi_split, (case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30) when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30) when sku_type = 'Not Moving' then 0 end )*Makassar_split Makassar_split, (case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30) when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30) when sku_type = 'Not Moving' then 0 end )*pekanbaru_medan_split pekanbaru_medan_split, (case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30) when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30) when sku_type = 'Not Moving' then 0 end )*Semarang_split Semarang_split, (case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30) when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30) when sku_type = 'Not Moving' then 0 end )*Surabaya_split Surabaya_split FROM raena_analytics.basic_calculation_stage4 A LEFT JOIN raena_analytics.basic_calculation_stage6 B ON A.sku =B.sku LEFT join raena_analytics.basic_calculation_stage5 C on A.sku=C.sku; drop table if exists raena_analytics.inbound_data; create table raena_analytics.inbound_data as select raena_code sku, sum(case when warehouse_id = 'CGK WHS' then COALESCE(quantity,0) end) Cikarang_quantity , sum(case when warehouse_id = 'Makassar WHS' then COALESCE(quantity,0) end) Makassar_quantity , sum(case when warehouse_id = 'Medan WHS' then COALESCE(quantity,0) end) medan_quantity, sum(case when warehouse_id = 'Samarinda WHS' then COALESCE(quantity,0) end) Samarinda_quantity , sum(case when warehouse_id = 'Semarang WHS' then COALESCE(quantity,0) end) semarang_quantity , sum(case when warehouse_id = 'Surabaya WHS' then COALESCE(quantity,0) end) Surabaya_quantity from (select * from raena_erp_management.inbound_order where cast(expected_arrival_date as date) >'2024-06-23' and received_time is null ) A left join raena_erp_management.inbound_order_sku B on A.id = B."orderIdId" group by raena_code; drop table if exists raena_analytics.warehouse_in_stock; create table raena_analytics.warehouse_in_stock as select sku , sum(case when F.warehouse_code = 'WH-CGK45' then coalesce(F.stock_limit,0) end) Cikarang_in_stock, sum(case when F.warehouse_code = 'WH-SUB51' then coalesce(F.stock_limit,0) end) Surabaya_in_stock, sum(case when F.warehouse_code = 'WH-SRG18' then coalesce(F.stock_limit,0) end) Semarang_in_stock, sum(case when F.warehouse_code = 'WH-MES07' then coalesce(F.stock_limit,0) end) Medan_in_stock, sum(case when F.warehouse_code = 'WH-UPG04' then coalesce(F.stock_limit,0) end) Makassar_in_stock, sum(case when F.warehouse_code = 'WH-AAP02' then coalesce(F.stock_limit,0) end) Samarinda_in_stock from raena_catalog_management.product C left join raena_catalog_management.product_inventory F on C.id = F.product_id group by sku; DROP TABLE IF EXISTS raena_analytics.demand_forecast_dashboard_base; CREATE TABLE raena_analytics.demand_forecast_dashboard_base AS SELECT DISTINCT A.sku , A.brand_name , case when B.sku_type is not null then B.sku_type else 'Not Moving' end sku_type , case when new_sku_flag is not null then new_sku_flag else 'No' end new_sku_flag, A.sourcing, A.ordering_type , A.active_status, multiple_flag, moq, moqremark, give_away, --cast(case when sourcing = 'Local' THEN 14*overall_qty_required_per_day -- WHEN sourcing = 'Crossborder' THEN 30*overall_qty_required_per_day end as int) overall_qty_required_per_day, cast(Cikarang_in_stock as int) Cikarang_in_stock, cast(coalesce(E.Cikarang_quantity,0) as int) Cikarang_quantity_in_transit, cast((Cikarang_in_stock+ coalesce(E.Cikarang_quantity,0)) as int) total_Cikarang_stock, cast(l30d_runrate as int) Cikarang_runrate, cast(CASE WHEN l30d_runrate = 0 THEN 0 else((Cikarang_in_stock+ coalesce(E.Cikarang_quantity,0))*(1.1)/l30d_runrate) END as int) Cikarang_Inventory_day_for_future, cast(bekasi_total_lead_time as int) cikarang_total_lead_time , CASE WHEN CASE WHEN l30d_runrate = 0 THEN 0 else((Cikarang_in_stock+ coalesce(E.Cikarang_quantity,0))*(1.1)/l30d_runrate) END