testing

PHOTO EMBED

Tue Nov 23 2021 08:18:25 GMT+0000 (UTC)

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)
content_copyCOPY