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) )
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter