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