testing
Tue Nov 23 2021 08:18:25 GMT+0000 (Coordinated Universal Time)
Saved by @emdzungshopee #script
WITH fx AS (SELECT * FROM order_mart_dim_exchange_rate WHERE grass_region = 'VN') , flash_sale AS (SELECT * FROM shopee_vn_bi_team__airpay_sponsor_flashsale_data WHERE ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__airpay_sponsor_flashsale_data) UNION (SELECT * FROM shopee_vn_bi_team__airpay_sponsor_flashsale_new_batch WHERE ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__airpay_sponsor_flashsale_new_batch) AND product_id IS NOT NULL) UNION (SELECT * FROM shopee_vn_bi_team__airpay_sponsor_flashsale_new_scheme WHERE ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__airpay_sponsor_flashsale_new_scheme) AND product_id IS NOT NULL) ) , evoucher_deal AS (SELECT DISTINCT * FROM shopee_vn_bi_team__monthly_voucher_deal_airpay f WHERE ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__monthly_voucher_deal_airpay) ) , collection_data AS (SELECT * FROM shopee_vn_bi_team__airpay_collection_sponsor_data WHERE ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__airpay_collection_sponsor_data) AND status = 'Confirmed') , fs_airpay_raw AS ( ( SELECT order_id , o.item_id , model_id , group_id , bundle_order_item_id , 'FS' as price_point FROM ((( SELECT DISTINCT * FROM flash_sale ) f INNER JOIN shopee_vn.order_mart_dwd_order_item_all_event_final_status_df o ON (((TRY_CAST(f.product_id AS bigint) = o.item_id) AND (TRY_CAST(f.date AS date) = "date"("split"(o.create_datetime, ' ')[1]))) AND (o.item_promotion_source = 'flash_sale'))) LEFT JOIN fx f ON (f.grass_date = "date"("split"(o.create_datetime, ' ')[1]))) WHERE "date"("split"(o.create_datetime, ' ')[1]) BETWEEN date'2020-01-01' and current_date - interval '1' day AND (o.payment_method_id IN (28, 30, 5)) ) UNION ( SELECT order_id , o.item_id , model_id , group_id , bundle_order_item_id , 'Evoucher' as price_point FROM ((( SELECT DISTINCT * FROM evoucher_deal ) f INNER JOIN shopee_vn.order_mart_dwd_order_item_all_event_final_status_df o ON (TRY_CAST(f.itemid AS bigint) = o.item_id)) LEFT JOIN fx f ON (f.grass_date = "date"("split"(o.create_datetime, ' ')[1]))) ) UNION ( SELECT order_id , o.item_id , model_id , group_id , bundle_order_item_id , 'Col' as price_point FROM ((( SELECT DISTINCT * FROM collection_data ) c INNER JOIN shopee_vn.order_mart_dwd_order_item_all_event_final_status_df o ON (((TRY_CAST(c.itemid AS bigint) = o.item_id) AND (TRY_CAST(c.grass_date AS date) = "date"("split"(o.create_datetime, ' ')[1]))) AND (item_promotion_source <> 'flash_sale'))) LEFT JOIN fx f ON (f.grass_date = "date"("split"(o.create_datetime, ' ')[1]))) WHERE "date"("split"(o.create_datetime, ' ')[1]) BETWEEN date'2020-01-01' and current_date - interval '1' day AND (o.payment_method_id IN (28, 30, 5)) ) ) , sellers AS (SELECT shop_id , SUM(order_fraction) / 30 AS ado , SUM(gmv_usd) / 30 AS adgmv FROM order_mart_dwd_order_item_all_event_final_status_df WHERE DATE(SPLIT(create_datetime, ' ')[1]) BETWEEN DATE_ADD('day',-29,date_trunc('day', from_iso8601_date('2020-07-01') - interval '1' day)) AND from_iso8601_date('2020-07-01') AND shop_id NOT IN (134344983 , 145418026 , 23921767 ) GROUP BY 1 ) , seller_type AS (SELECT s.shop_id, CASE WHEN u.is_cb_shop = 1 THEN 'Cross-border' WHEN u.is_official_shop = 1 THEN 'Mall' WHEN s.ado >= 20 THEN 'Short-tail' WHEN s.ado >= 5 AND s.ado < 20 THEN 'Mid-tail' WHEN s.ado < 5 THEN 'Long-tail' ELSE 'Other' END AS seller_type FROM sellers s LEFT JOIN user_mart_dim_shop u ON s.shop_id = u.shop_id WHERE u.grass_date = current_date - interval '1' day ) , base AS ( SELECT o.* , 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 , 1/ CAST(CARDINALITY(ARRAY_DISTINCT(TRANSFORM(kpi_categories, x -> SLICE(x, 1, 1)))) AS DOUBLE) l1_factor FROM shopee_vn.order_mart_dwd_order_item_all_event_final_status_df o WHERE DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13') AND is_bi_excluded = 0 ) , order_mart AS ( SELECT b.* , SPLIT_PART(kpi_cat.kpi_category[1],':', 1) l1_kpi_category_id , SPLIT_PART(kpi_cat.kpi_category[1],':', 2) l1_kpi_category , SPLIT_PART(kpi_cat.kpi_category[2],':', 1) l2_kpi_category_id , SPLIT_PART(kpi_cat.kpi_category[2],':', 2) l2_kpi_category , SPLIT_PART(TRY(kpi_cat.kpi_category[3]),':', 1) l3_kpi_category_id , SPLIT_PART(try(kpi_cat.kpi_category[3]),':', 2) l3_kpi_category , (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 base b CROSS JOIN unnest (b.kpi_categories) as kpi_cat(kpi_category) ) , fs_list AS ( SELECT DISTINCT TRY_CAST(grass_date AS DATE) grass_date, batch, TRY_CAST(itemid AS BIGINT) item_id, TRY_CAST(modelid AS BIGINT) model_id, TRY_CAST(start_time AS TIMESTAMP) start_time, TRY_CAST(end_time AS TIMESTAMP) end_time, TRY_CAST(promo_price AS BIGINT) promo_price, price_point, TRY_CAST(stock AS BIGINT) stock FROM shopee_vn_bi_team__lpp_new_deal_list_v1 WHERE format_type IS NULL AND ingestion_timestamp != 'a' ), col_list_before AS ( SELECT DISTINCT TRY_CAST(o.grass_date AS DATE) grass_date, TRY_CAST(itemid AS BIGINT) item_id, TRY_CAST(modelid AS BIGINT) model_id, TRY_CAST(stock AS BIGINT) stock FROM shopee_vn_bi_team__lpp_collection_list_v1 o WHERE TRY_CAST(o.grass_date AS DATE) = DATE '2021-03-03' AND ingestion_timestamp != 'a' ), col_list_after AS ( SELECT DISTINCT TRY_CAST(o.grass_date AS DATE) grass_date, TRY_CAST(itemid AS BIGINT) item_id, TRY_CAST(stock AS BIGINT) stock FROM shopee_vn_bi_team__lpp_collection_list_v1 o WHERE TRY_CAST(o.grass_date AS DATE) >= DATE '2021-03-08' AND ingestion_timestamp NOT IN (SELECT exclude_timestamp FROM shopee_vn_bi_team__lpp_exclude_issue) ), col_nb AS ( SELECT DISTINCT TRY_CAST(o.grass_date AS DATE) grass_date, TRY_CAST(itemid AS BIGINT) item_id, TRY_CAST(stock AS BIGINT) stock FROM shopee_vn_bi_team__collection_deals_nb o WHERE ingestion_timestamp != 'a' ), lpp_fs_orders_before AS ( SELECT DISTINCT order_id, f.item_id, f.model_id, group_id, bundle_order_item_id, price_point FROM order_mart_dwd_order_item_all_event_final_status_df o JOIN fs_list f ON DATE(SPLIT(o.create_datetime, ' ')[1]) = DATE(f.grass_date) AND o.item_id = f.item_id AND o.model_id = f.model_id AND FROM_UNIXTIME(create_timestamp - 3600) BETWEEN start_time AND end_time WHERE f.item_id IS NOT NULL AND item_promotion_source = 'flash_sale' AND DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN DATE '2021-02-28' AND DATE '2021-03-07' AND item_rebate_by_shopee_amt > 0 ), lpp_fs_orders_after AS ( SELECT DISTINCT order_id, f.item_id, f.model_id, group_id, bundle_order_item_id, price_point FROM order_mart_dwd_order_item_all_event_final_status_df o JOIN fs_list f ON DATE(SPLIT(o.create_datetime, ' ')[1]) = DATE(f.grass_date) AND o.item_id = f.item_id AND o.model_id = f.model_id AND FROM_UNIXTIME(create_timestamp - 3600) BETWEEN start_time AND end_time WHERE f.item_id IS NOT NULL AND item_promotion_source = 'flash_sale' AND DATE(SPLIT(o.create_datetime, ' ')[1]) >= DATE '2021-03-08' ), lpp_col_orders_before AS ( SELECT DISTINCT order_id, c.item_id, c.model_id, group_id, bundle_order_item_id, 'Collection' AS price_point FROM order_mart_dwd_order_item_all_event_final_status_df o JOIN col_list_before c ON o.item_id = c.item_id AND o.model_id = c.model_id AND DATE(SPLIT(o.create_datetime, ' ')[1]) = c.grass_date WHERE DATE(SPLIT(o.create_datetime, ' ')[1]) = DATE '2021-03-03' AND (item_promotion_source != 'flash_sale' OR item_promotion_source IS NULL) AND item_rebate_by_shopee_amt > 0 ), lpp_col_orders_after AS ( SELECT DISTINCT order_id, c.item_id, model_id, group_id, bundle_order_item_id, 'Collection' AS price_point FROM order_mart_dwd_order_item_all_event_final_status_df o JOIN col_list_after c ON o.item_id = c.item_id AND DATE(SPLIT(o.create_datetime, ' ')[1]) = c.grass_date WHERE DATE(SPLIT(o.create_datetime, ' ')[1]) >= DATE '2021-03-08' AND (item_promotion_source != 'flash_sale' OR item_promotion_source IS NULL) ), lpp_col_orders_nb AS ( SELECT DISTINCT order_id, c.item_id, model_id, group_id, bundle_order_item_id, 'NB' AS price_point FROM order_mart_dwd_order_item_all_event_final_status_df o JOIN col_nb c ON o.item_id = c.item_id AND DATE(SPLIT(o.create_datetime, ' ')[1]) = c.grass_date WHERE DATE(SPLIT(o.create_datetime, ' ')[1]) >= DATE '2021-05-18' AND (item_promotion_source != 'flash_sale' OR item_promotion_source IS NULL) ), lpp_fs_orders AS ( SELECT * FROM lpp_fs_orders_before UNION SELECT * FROM lpp_fs_orders_after UNION SELECT * FROM lpp_col_orders_before UNION SELECT * FROM lpp_col_orders_after UNION SELECT * FROM lpp_col_orders_nb ) , nonbd_orders AS ( SELECT * FROM lpp_fs_orders UNION SELECT * FROM fs_airpay_raw ) , list AS ( SELECT o.shop_id , o.l1_kpi_category , CAST(SUM(o.order_fraction * l3_factor) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_gross_ado , CAST(COUNT(DISTINCT o.order_id) AS DOUBLE)/(DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_gross_ado_distinct , CAST(SUM(o.gmv_usd * l3_factor) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_gross_daily_gmv , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.item_amount ELSE 0 END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS l30d_net_qty , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.order_fraction * l3_factor END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_net_ado , CAST(COUNT(DISTINCT(CASE WHEN is_net_order = 1 THEN o.order_id END)) AS DOUBLE)/(DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_net_ado_distinct , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.gmv_usd * l3_factor END) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_net_daily_gmv , COUNT(DISTINCT o.item_id) AS skus , SUM(o.item_amount) AS qty , sum(case when is_net_order = 1 and regexp_like(pv_voucher_code,'^(MA|WA|KID|FASHION|FA|MS|SO|WB|WS|WBS|WTCH).*') then pv_rebate_by_shopee_amt_usd + sv_rebate_by_shopee_amt_usd end) bd_voucher_direct , sum(case when is_net_order = 1 and regexp_like(pv_voucher_code,'^(MA|WA|KID|FASHION|FA|MS|SO|WB|WS|WBS|WTCH).*') then pv_coin_earn_by_shopee_amt_usd + sv_coin_earn_by_shopee_amt_usd end) bd_coin_direct , sum(case when is_net_order = 1 and not regexp_like(pv_voucher_code,'^(MA|WA|KID|FASHION|FA|MS|SO|WB|WS|WBS|WTCH).*') then pv_rebate_by_shopee_amt_usd + sv_rebate_by_shopee_amt_usd end) non_bd_voucher_direct , sum(case when is_net_order = 1 and not regexp_like(pv_voucher_code,'^(MA|WA|KID|FASHION|FA|MS|SO|WB|WS|WBS|WTCH).*') then pv_coin_earn_by_shopee_amt_usd + sv_coin_earn_by_shopee_amt_usd end) non_bd_coin_direct , sum(case when is_net_order = 1 and non_o.order_id is null then item_rebate_by_shopee_amt_usd * l3_factor end) rebate FROM order_mart o LEFT JOIN nonbd_orders non_o ON o.order_id = non_o.order_id AND o.item_id = non_o.item_id AND o.model_id = non_o.model_id AND o.group_id = non_o.group_id AND o.bundle_order_item_id = non_o.bundle_order_item_id WHERE l1_kpi_category IN ('Fashion Accessories','Women Clothes','Men Bags','Women Bags','Women Shoes','Men Shoes','Men Clothes','Kid Fashion','Watches','Sport & Outdoor') GROUP BY 1,2 ) , all_cluster AS ( SELECT o.shop_id , o.l1_kpi_category , CASE WHEN l1_kpi_category IN ('Health','Beauty','Pets','Home care','Moms, Kids & Babies','Grocery') THEN 'FMCG' WHEN l1_kpi_category IN ('Fashion Accessories','Women Clothes','Men Bags','Women Bags','Women Shoes','Men Shoes','Men Clothes','Kid Fashion','Watches','Sport & Outdoor') THEN 'Fashion' WHEN l1_kpi_category IN ('Home Appliances','Computer & Accessories','Cameras','Consumer Electronics','Mobile & Gadgets','Tickets, Vouchers & Services') THEN 'ELHA' WHEN l1_kpi_category IN ('Home & Living','Books','Automotive','Toys') THEN 'Lifestyle' ELSE 'Unknown' END AS group_category , CAST(SUM(o.order_fraction * l3_factor) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS platform_gross_ado , CAST(SUM(o.gmv_usd * l3_factor) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS platform_gross_daily_gmv , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.gmv_usd * l3_factor END) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS platform_net_daily_gmv , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.order_fraction * l3_factor END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS l30d_net_ado , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.item_amount ELSE 0 END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS l30d_net_qty , COUNT(DISTINCT o.item_id) AS sale_sku FROM order_mart o GROUP BY 1,2,3 ) , ranking AS ( SELECT shop_id , group_category , l1_kpi_category , ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY l30d_net_ado DESC, sale_sku DESC, l30d_net_qty DESC) AS ranking FROM all_cluster ) , new_cat AS ( SELECT shop_id , MIN_BY(l1_kpi_category,ranking) AS main_cat , MIN_BY(group_category,ranking) AS group_cat FROM ranking GROUP BY 1 ) , gross AS ( SELECT n.shop_id , CAST(SUM(IF(DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13'),o.order_fraction * l3_factor,0)) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS gross_ado , CAST(SUM(IF(DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13'),o.gmv_usd * l3_factor,0)) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS gross_daily_gmv FROM new_cat n LEFT JOIN order_mart o ON n.shop_id = o.shop_id GROUP BY 1 ) , net AS ( SELECT n.shop_id , CAST(SUM(IF(DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13'), CASE WHEN o.is_net_order = 1 THEN o.order_fraction * l3_factor END,0)) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS net_ado , CAST(SUM(IF(DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13'), CASE WHEN o.is_net_order = 1 THEN o.gmv_usd * l3_factor END,0)) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS net_daily_gmv FROM new_cat n LEFT JOIN order_mart o ON n.shop_id = o.shop_id GROUP BY 1 ) , main_subcat AS ( SELECT r.shop_id , r.main_cat , o.l2_kpi_category , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.order_fraction * l3_factor ELSE 0 END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'), from_iso8601_date('2020-07-13')) + 1) AS subcat_l30d_net_ado , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.item_amount ELSE 0 END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'), from_iso8601_date('2020-07-13')) + 1) AS subcat_l30d_net_qty , COUNT(DISTINCT o.item_id) AS subcat_sale_sku FROM new_cat r JOIN order_mart o ON r.shop_id = o.shop_id AND r.main_cat = o.l1_kpi_category AND DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13') AND l1_kpi_category IN ('Fashion Accessories','Women Clothes','Men Bags','Women Bags','Women Shoes','Men Shoes','Men Clothes','Kid Fashion','Watches','Sport & Outdoor') GROUP BY 1,2,3 ) , subcat_ranking AS ( SELECT shop_id , main_cat , l2_kpi_category , ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY subcat_l30d_net_ado DESC, subcat_sale_sku DESC, subcat_l30d_net_qty DESC) AS ranking FROM main_subcat ) , new_subcat AS ( SELECT shop_id , MIN_BY(main_cat,ranking) AS main_cat , MIN_BY(l2_kpi_category,ranking) AS sub_cat FROM subcat_ranking GROUP BY 1 ) , all AS (SELECT DISTINCT "date_parse"(effective_dt, '%Y%m%d') cdate, log_id, shop_id, point FROM shopee_event_data__penalty_fact_tidb_penalty_history_log_tab_v2_da t WHERE country = 'VN' AND metrics_type = 0 AND metrics_id = 101 ANd manual_status = 1 AND auto_status = 1 AND "date_parse"(effective_dt, '%Y%m%d') >= IF(date_trunc('quarter', current_date - interval '1' day) > date_trunc('week',date_trunc('quarter',current_date - interval '1' day)) , date_trunc('week',date_trunc('quarter',current_date - interval '1' day)) + interval '7' day , date_trunc('week',date_trunc('quarter',current_date - interval '1' day)) ) ) , penalty_table AS (SELECT shop_id, SUM(point) as penalty_point FROM all GROUP BY 1) , nfr_table AS ( SELECT shop_id , nfr FROM shopee_event_data__penalty_aggr_nfr_7_res_v4_da WHERE utc_date = date_trunc('week',current_date) - interval '1' day ) , lsr_table AS ( SELECT shop_id , lsr FROM shopee_event_data__penalty_aggr_lsr_7_res_v4_da WHERE utc_date = date_trunc('week',current_date) - interval '1' day ) , preorder AS ( SELECT shopid, SUM(if(grass_date = DATE_TRUNC('week',CURRENT_DATE),"preorder_listing_%",0)) as preorder_percent FROM shopee_bi_vn_preorder_listings_shop WHERE grass_date >= DATE_TRUNC('week',CURRENT_DATE) - interval '30' day AND grass_date <= DATE_TRUNC('week',CURRENT_DATE) GROUP BY 1 ) , ps_week_ago AS ( SELECT DISTINCT shop_id FROM user_mart_dim_shop WHERE grass_date = date_trunc('week',date_trunc('week',current_date) - interval '1' day) AND is_preferred_shop = 1 ) , raw AS ( SELECT l.shop_id , u.user_name , n.group_cat , n.main_cat , l.l1_kpi_category AS multiple_fashion_cat , COALESCE(l.fashion_gross_ado, 0) AS multiple_fashion_cat_gross_ado , COALESCE(l.fashion_gross_ado_distinct,0) AS multiple_fashion_cat_distinct_ado , COALESCE(l.fashion_gross_daily_gmv, 0) AS multiple_fashion_cat_gross_daily_gmv , COALESCE(g.gross_ado, 0) AS platform_gross_ado , COALESCE(g.gross_daily_gmv, 0) AS platform_gross_daily_gmv , COALESCE(l.fashion_net_ado, 0) AS multiple_fashion_cat_net_ado , COALESCE(l.fashion_net_ado_distinct,0) AS multiple_fashion_cat_net_distinct_ado , COALESCE(l.fashion_net_daily_gmv, 0) AS multiple_fashion_cat_net_daily_gmv , COALESCE(net.net_ado, 0) AS platform_net_ado , COALESCE(net.net_daily_gmv, 0) AS platform_net_daily_gmv , COALESCE(l.skus, 0) AS total_selling_skus , COALESCE(l.qty, 0) AS quantity_sold , COALESCE(l.bd_voucher_direct, 0) AS bd_voucher_direct , COALESCE(l.bd_coin_direct, 0) AS bd_coin_direct , COALESCE(l.non_bd_voucher_direct, 0) AS non_bd_voucher_direct , COALESCE(l.non_bd_coin_direct, 0) AS non_bd_coin_direct , COALESCE(l.rebate, 0) AS rebate , u.is_official_shop AS is_mall , u.is_cb_shop AS is_cb , CASE WHEN u.status = 0 THEN 'Delete' WHEN u.status = 1 THEN 'Normal' WHEN u.status = 2 THEN 'Banned' WHEN u.status = 3 THEN 'Frozen' ELSE 'Null' END AS seller_status , CASE WHEN s.shop_id IS NOT NULL THEN s.seller_type WHEN s.shop_id IS NULL AND u.is_cb_shop = 1 THEN 'Cross-border' WHEN s.shop_id IS NULL AND u.is_official_shop = 1 THEN 'Mall' ELSE 'Long-tail' END AS seller_type , sub_cat AS main_subcat , nfr.nfr , lsr.lsr , p.penalty_point , pre.preorder_percent , CASE WHEN ps_week_ago.shop_id IS NULL AND u.is_preferred_shop = 0 THEN 'NO PS' WHEN ps_week_ago.shop_id IS NULL AND u.is_preferred_shop = 1 THEN 'NEW PS' WHEN ps_week_ago.shop_id IS NOT NULL AND u.is_preferred_shop = 0 THEN 'OFF PS' WHEN ps_week_ago.shop_id IS NOT NULL AND u.is_preferred_shop = 1 THEN 'CURRENT PS' end as is_PS FROM list l LEFT JOIN new_cat n ON l.shop_id = n.shop_id LEFT JOIN new_subcat r2 ON l.shop_id = r2.shop_id LEFT JOIN seller_type s ON l.shop_id = s.shop_id LEFT JOIN user_mart_dim_shop u ON l.shop_id = u.shop_id LEFT JOIN penalty_table p ON l.shop_id = p.shop_id LEFT JOIN nfr_table nfr ON l.shop_id = nfr.shop_id LEFT JOIN lsr_table lsr ON l.shop_id = lsr.shop_id LEFT JOIN preorder pre ON l.shop_id = pre.shopid LEFT JOIN ps_week_ago ON ps_week_ago.shop_id = l.shop_id LEFT JOIN gross g ON l.shop_id = g.shop_id LEFT JOIN net ON l.shop_id = net.shop_id WHERE u.grass_date = current_date - interval '1' day AND u.is_cb_shop IN (0) AND u.is_official_shop IN (0) ) SELECT DISTINCT * FROM raw WHERE shop_id IN (0) OR 0 IN (0)
Comments