with            order_base as 
(                
select           soi.*
                ,1/cast( coalesce(cardinality(array_distinct(transform(kpi_categories, x -> slice(x, 1, 1)))), 1) as double) as l1_factor
                ,array_distinct(transform(kpi_categories, x -> slice(x, 1, 1))) level1_kpi_categories
                ,array_distinct(transform(kpi_categories, x -> slice(x, 1, 2))) level2_kpi_categories
                ,array_distinct(transform(kpi_categories, x -> slice(x, 1, 3))) level3_kpi_categories
                ,date(cast(soi.create_datetime as timestamp)) as order_place_date
                ,case when lpp.model_id is not null then 'LPP' else 'Others' end as LPPFlag
from             mp_order.dwd_order_item_all_ent_df__vn_s0_live soi 
left join        vnbi_bd.stg_dwd_lpp_order_item lpp
on               soi.order_id = lpp.order_id
                 and lpp.item_id = soi.item_id 
                 and lpp.model_id = soi.model_id 
                 and lpp.group_id = soi.group_id 
                 and lpp.bundle_order_item_id = soi.bundle_order_item_id        
where            --lpp.model_id is null
                 soi.grass_date >= date'2021-01-01'
                 and soi.is_bi_excluded = 0
                 and date(cast(soi.create_datetime as timestamp)) >= date'2021-01-01'
),
                 order_base_add_factor as 
(
select            ob.*
                ,split_part(kpi_cat.kpi_category[1],':', 1) as l1_kpi_category_id
                ,split_part(kpi_cat.kpi_category[1],':', 2) as l1_kpi_category
                ,split_part(kpi_cat.kpi_category[2],':', 1) as l2_kpi_category_id
                ,split_part(kpi_cat.kpi_category[2],':', 2) as l2_kpi_category
                ,split_part(try(kpi_cat.kpi_category[3]),':', 1) as l3_kpi_category_id
                ,split_part(try(kpi_cat.kpi_category[3]),':', 2) as l3_kpi_category
                ,l1_factor
                ,(1/cast(coalesce(cardinality(array_distinct(filter(level2_kpi_categories, x -> contains(x, kpi_cat.kpi_category[1]) ))), 1) as double)) * l1_factor as l2_factor
                ,(1/cast(coalesce(cardinality(array_distinct(filter(level3_kpi_categories, x -> contains(x, kpi_cat.kpi_category[2]) ))), 1) as double)) * (1 / cast(coalesce(cardinality(array_distinct(filter(level2_kpi_categories, x -> contains(x, kpi_cat.kpi_category[1]) ))), 1) as double))* l1_factor as l3_factor
from             order_base ob 
left join unnest (ob.level3_kpi_categories) as kpi_cat(kpi_category) on true    
),
                ordermart as 
(
select           o.order_id
                ,c.cluster
                ,o.l1_kpi_category
                ,o.l2_kpi_category
                ,o.l3_kpi_category
                ,o.item_id
                ,i.name as item_name
                ,o.model_id
                ,o.shop_id
                ,o.is_cb_shop
                ,o.LPPFlag
                ,i.mtsku_item_id
                ,o.order_place_date
                ,o.is_flash_sale 
                ,o.order_fraction*l3_factor as order_fraction
                ,o.gmv*l3_factor as gmv
                ,o.gmv_usd*l3_factor as gmv_usd
                ,o.item_amount*l3_factor as item_amount
                ,o.item_price_before_discount_pp_usd *l3_factor as item_price_before_discount_pp_usd
                ,o.item_price_before_discount_pp*l3_factor as item_price_before_discount_pp
                ,o.item_input_price_pp*l3_factor as item_input_price_pp
                ,o.item_input_price_pp_usd*l3_factor as item_input_price_pp_usd
                ,o.item_rebate_by_seller_amt*l3_factor as item_rebate_by_seller_amt
                ,o.item_rebate_by_seller_amt_usd*l3_factor as item_rebate_by_seller_amt_usd
                ,o.item_rebate_by_shopee_amt*l3_factor as item_rebate_by_shopee_amt
                ,o.item_rebate_by_shopee_amt_usd*l3_factor as item_rebate_by_shopee_amt_usd
                ,o.merchandise_subtotal_amt*l3_factor as merchandise_subtotal_amt
                ,o.merchandise_subtotal_amt_usd*l3_factor as merchandise_subtotal_amt_usd
                ,i.price as current_price
from            order_base_add_factor o     
inner join      mp_item.dim_item__vn_s0_live i
on              i.item_id = o.item_id
left join       vnbi_bd.shopee_vn_bi_team__kpi_category_cluster_mapping c 
on              c.l1_kpi_category_recat = o.l1_kpi_category
and c.ingestion_timestamp = (select max(ingestion_timestamp) from vnbi_bd.shopee_vn_bi_team__kpi_category_cluster_mapping)
where           i.grass_date = date_add('day',-1,current_date)            
)