911 lines
32 KiB
MySQL
911 lines
32 KiB
MySQL
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.manual_bundle_sku_data ;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.manual_bundle_sku_data AS
|
||
|
|
SELECT upper(bundle_sku) Parent_sku ,
|
||
|
|
Upper(A.sku) child_sku ,
|
||
|
|
A.quantity child_quantity,
|
||
|
|
'Bundle' parent_sku_class,
|
||
|
|
cast(B.retail_price AS decimal(22,2)) bundle_retail_price,
|
||
|
|
cast(json_extract_path_text(C.tier_price,'07030fbe-5801-4318-9e97-fe33fa169894',TRUE) AS decimal(22,2)) bronze_price,
|
||
|
|
cast(json_extract_path_text(C.tier_price,'8eb95d6e-915a-4a91-9c12-fa43db995e19',TRUE) AS decimal(22,2)) silver_price,
|
||
|
|
cast(json_extract_path_text(C.tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) AS decimal(22,2)) gold_price,
|
||
|
|
cast(C.retail_price AS decimal(22,2)) child_retail_price
|
||
|
|
FROM bundle_data_manual_new A
|
||
|
|
INNER JOIN raena_catalog_management.product B ON upper(A.bundle_sku) = upper(B.sku)
|
||
|
|
INNER JOIN raena_catalog_management.product C ON upper(A.sku) = upper(C.sku);
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.order_level_data;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.order_level_data AS
|
||
|
|
SELECT external_id,
|
||
|
|
transaction_date AS transaction_date,
|
||
|
|
discount_amount,
|
||
|
|
shipping_cost,
|
||
|
|
A.coupon_code,
|
||
|
|
reseller_tier_name,
|
||
|
|
CASE
|
||
|
|
WHEN flash_sale_id IS NOT NULL THEN 'Flash'
|
||
|
|
END Product_type,
|
||
|
|
order_loyalty_discount,
|
||
|
|
total_amount ,
|
||
|
|
total_retail_price ,
|
||
|
|
tier_discount,
|
||
|
|
total_dynamic_and_tier_price,
|
||
|
|
payment_amount,
|
||
|
|
coupon_applied_on
|
||
|
|
FROM
|
||
|
|
(SELECT A.id AS external_id ,
|
||
|
|
(A.created_at) AS transaction_date ,
|
||
|
|
A.discount_amount,
|
||
|
|
applied_shipping_amount shipping_cost,
|
||
|
|
A.coupon_code,
|
||
|
|
json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
|
||
|
|
flash_sale_id,
|
||
|
|
loyalty_discount AS order_loyalty_discount,
|
||
|
|
total_amount ,
|
||
|
|
total_retail_price ,
|
||
|
|
tier_discount,
|
||
|
|
total_dynamic_and_tier_price,
|
||
|
|
payment_amount
|
||
|
|
FROM raena_order_management.order A
|
||
|
|
WHERE payment_status='Paid'
|
||
|
|
AND cast(A.created_at AS date) >='2022-05-24'
|
||
|
|
AND is_campaign = 'false') A
|
||
|
|
LEFT JOIN raena_order_management.discount_coupon C ON A.coupon_code = C.coupon_code;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.base_netsuite_stage1;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.base_netsuite_stage1 AS
|
||
|
|
SELECT DISTINCT transaction_date,
|
||
|
|
A.external_id,
|
||
|
|
B.country,
|
||
|
|
CASE
|
||
|
|
WHEN cast(B.sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_sku
|
||
|
|
ELSE B.sku
|
||
|
|
END sku,
|
||
|
|
CASE
|
||
|
|
WHEN B.parent_item_id = F.id THEN F.sku
|
||
|
|
ELSE B.sku
|
||
|
|
END parent_sku,
|
||
|
|
CASE
|
||
|
|
WHEN cast(B.sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_quantity*B.quantity
|
||
|
|
ELSE B.quantity
|
||
|
|
END quantity,
|
||
|
|
CASE
|
||
|
|
WHEN B.parent_item_id = F.id THEN F.quantity
|
||
|
|
ELSE B.quantity
|
||
|
|
END parent_quantity,
|
||
|
|
CASE
|
||
|
|
WHEN cast(B.sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_retail_price
|
||
|
|
ELSE B.retail_price
|
||
|
|
END retail_price,
|
||
|
|
CASE
|
||
|
|
WHEN B.parent_item_id = F.id THEN F.retail_price
|
||
|
|
ELSE B.retail_price
|
||
|
|
END parent_retail_price,
|
||
|
|
CASE
|
||
|
|
WHEN cast(B.sku AS varchar) = cast(D.parent_sku AS varchar)
|
||
|
|
AND reseller_tier_name = 'BRONZE' THEN bronze_price
|
||
|
|
WHEN cast(B.sku AS varchar) = cast(D.parent_sku AS varchar)
|
||
|
|
AND reseller_tier_name = 'SILVER' THEN silver_price
|
||
|
|
WHEN cast(B.sku AS varchar) = cast(D.parent_sku AS varchar)
|
||
|
|
AND reseller_tier_name = 'GOLD' THEN gold_price
|
||
|
|
ELSE B.wholesale_price
|
||
|
|
END wholesale_price,
|
||
|
|
CASE
|
||
|
|
WHEN parent_sku LIKE 'BAZ%' THEN B.wholesale_price
|
||
|
|
WHEN B.parent_item_id = F.id THEN F.wholesale_price
|
||
|
|
ELSE B.wholesale_price
|
||
|
|
END AS parent_wholesale_price,
|
||
|
|
CASE
|
||
|
|
WHEN B.parent_item_id = F.id THEN B.retail_price*B.quantity*F.discount_price / sum(CASE WHEN B.parent_item_id = F.id THEN B.retail_price*B.quantity END) over(partition BY external_id ,F.sku)
|
||
|
|
ELSE B.discount_price
|
||
|
|
END discount_price,
|
||
|
|
coupon_applied_on,
|
||
|
|
A.discount_amount,
|
||
|
|
CASE
|
||
|
|
WHEN B.parent_item_id IS NULL
|
||
|
|
AND cast(B.sku AS varchar) = cast(D.parent_sku AS varchar) THEN 0
|
||
|
|
ELSE B.dynamic_price
|
||
|
|
END Dynamic_price ,
|
||
|
|
CASE
|
||
|
|
WHEN B.parent_item_id = F.id THEN F.dynamic_price*F.quantity
|
||
|
|
WHEN B.parent_item_id IS NULL
|
||
|
|
AND cast(B.sku AS varchar) = cast(D.parent_sku AS varchar) THEN B.dynamic_price*B.quantity
|
||
|
|
END AS parent_dynamic_price,
|
||
|
|
B.payment_price,
|
||
|
|
payment_amount,
|
||
|
|
B.product_class,
|
||
|
|
coalesce(F.product_class,B.product_class) parent_product_class,
|
||
|
|
CASE
|
||
|
|
WHEN A.product_type = 'Flash'
|
||
|
|
AND B.sku LIKE 'BAZ%' THEN 'Flash Bundle'
|
||
|
|
WHEN A.product_type = 'Flash'
|
||
|
|
AND B.sku NOT LIKE 'BAZ%' THEN 'Flash'
|
||
|
|
WHEN B.sku LIKE 'BAZ%' THEN 'Bundle'
|
||
|
|
ELSE 'Regular'
|
||
|
|
END AS product_type_class,
|
||
|
|
F.id,
|
||
|
|
A.order_loyalty_discount,
|
||
|
|
B.loyalty_discount,
|
||
|
|
reseller_tier_name
|
||
|
|
FROM public.order_level_data A
|
||
|
|
LEFT JOIN raena_order_management.order_item B ON A.external_id = B.order_id
|
||
|
|
LEFT JOIN public.manual_bundle_sku_data D ON cast(B.sku AS varchar) = cast(D.parent_sku AS varchar)
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT id ,
|
||
|
|
order_id ,
|
||
|
|
retail_price,
|
||
|
|
Sku ,
|
||
|
|
quantity,
|
||
|
|
wholesale_price,
|
||
|
|
discount_price,
|
||
|
|
dynamic_price,
|
||
|
|
payment_price,
|
||
|
|
product_class,
|
||
|
|
loyalty_discount
|
||
|
|
FROM raena_order_management.order_item
|
||
|
|
WHERE product_class = 'Bundle'
|
||
|
|
AND cast(created_at AS date) >='2022-05-24') F ON B.parent_item_id = F.id
|
||
|
|
ORDER BY 1,
|
||
|
|
2;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.loyalty_point_calculation1;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.loyalty_point_calculation1 AS
|
||
|
|
SELECT *,
|
||
|
|
sum(order_loyalty_discount)over(partition BY external_id)/count(external_id)over(partition BY external_id) total_order_loyalty_discount ,
|
||
|
|
sum(loyalty_discount) over(partition BY external_id) total_sku_loyalty_discount
|
||
|
|
FROM public.base_netsuite_stage1
|
||
|
|
WHERE order_loyalty_discount >0;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.final_loyalty_point;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.final_loyalty_point AS
|
||
|
|
SELECT A.*,
|
||
|
|
CASE
|
||
|
|
WHEN sum(gold) over (partition BY external_id)>0
|
||
|
|
AND reseller_tier_name = 'GOLD' THEN gold*total_order_loyalty_discount/sum(gold) over (partition BY external_id)
|
||
|
|
WHEN sum(SILVER) over (partition BY external_id)>0
|
||
|
|
AND reseller_tier_name = 'SILVER' THEN SILVER*total_order_loyalty_discount/sum(SILVER) over (partition BY external_id)
|
||
|
|
WHEN sum(BRONZE) over (partition BY external_id)>0
|
||
|
|
AND reseller_tier_name = 'BRONZE' THEN bronze*total_order_loyalty_discount/sum(BRONZE) over (partition BY external_id)
|
||
|
|
END Final_loyalty_point
|
||
|
|
FROM public.loyalty_point_calculation1 A
|
||
|
|
LEFT JOIN loyalty_discount B ON A.sku = B.sku
|
||
|
|
WHERE total_order_loyalty_discount-total_sku_loyalty_discount NOT BETWEEN -10 AND 10;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.base_netsuite_stage2 ;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.base_netsuite_stage2 AS
|
||
|
|
SELECT transaction_date,
|
||
|
|
A.external_id,
|
||
|
|
A.sku,
|
||
|
|
country,
|
||
|
|
parent_sku,
|
||
|
|
quantity,
|
||
|
|
parent_quantity,
|
||
|
|
retail_price,
|
||
|
|
parent_retail_price,
|
||
|
|
CASE
|
||
|
|
WHEN parent_sku LIKE 'BAZ%'
|
||
|
|
AND id IS NULL THEN cast(wholesale_price*(parent_wholesale_price/quantity)/sum(wholesale_price)over(partition BY A.external_id ,parent_sku) AS decimal(22,2))
|
||
|
|
ELSE wholesale_price
|
||
|
|
END wholesale_price,
|
||
|
|
parent_wholesale_price,
|
||
|
|
discount_price,
|
||
|
|
coupon_applied_on,
|
||
|
|
discount_amount,
|
||
|
|
dynamic_price,
|
||
|
|
parent_dynamic_price,
|
||
|
|
payment_price,
|
||
|
|
payment_amount,
|
||
|
|
product_class,
|
||
|
|
parent_product_class,
|
||
|
|
product_type_class,
|
||
|
|
id,
|
||
|
|
CASE
|
||
|
|
WHEN cast(transaction_date AS date)< '2022-02-28'
|
||
|
|
AND A.external_id = B.external_id THEN B.final_loyalty_point
|
||
|
|
ELSE A.loyalty_discount
|
||
|
|
END loyalty_discount
|
||
|
|
FROM public.base_netsuite_stage1 A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT external_id ,
|
||
|
|
sku ,
|
||
|
|
final_loyalty_point
|
||
|
|
FROM public.final_loyalty_point) B ON A.external_id = B.external_id
|
||
|
|
AND A.sku = B.sku
|
||
|
|
WHERE product_class <> 'Bundle';
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.base_netsuite_stage3 ;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.base_netsuite_stage3 AS
|
||
|
|
SELECT A.* ,
|
||
|
|
CASE
|
||
|
|
WHEN dynamic_price>0 THEN dynamic_price
|
||
|
|
ELSE wholesale_price
|
||
|
|
END final_wholesale_price,
|
||
|
|
CASE
|
||
|
|
WHEN coupon_applied_on <> 'Cart'
|
||
|
|
AND discount_price>0 THEN retail_price*quantity*discount_amount/sum(CASE WHEN discount_price>0 THEN retail_price*quantity END)over(partition BY A.external_id)
|
||
|
|
WHEN coupon_applied_on <> 'Cart'
|
||
|
|
AND discount_price=0 THEN 0
|
||
|
|
WHEN coupon_applied_on ='Cart'
|
||
|
|
AND discount_price=0 THEN retail_price*quantity*discount_amount/sum(retail_price*quantity)over(partition BY A.external_id)
|
||
|
|
ELSE discount_price
|
||
|
|
END Final_discount ,
|
||
|
|
retail_price*quantity-(CASE
|
||
|
|
WHEN dynamic_price>0 THEN dynamic_price
|
||
|
|
ELSE wholesale_price
|
||
|
|
END)*quantity AS seller_margin
|
||
|
|
FROM public.base_netsuite_stage2 A ;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.base_netsuite_stage4 ;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.base_netsuite_stage4 AS
|
||
|
|
SELECT A.* ,
|
||
|
|
CASE
|
||
|
|
WHEN (parent_product_class= 'Bundle'
|
||
|
|
OR product_type_class in('Bundle','Flash Bundle'))
|
||
|
|
AND Final_discount>0
|
||
|
|
AND A.external_id = B.external_id
|
||
|
|
AND A.parent_sku = B.parent_sku THEN (cast(retail_price AS decimal(22,2))*quantity*(ttl_retail_price-(parent_retail_price*parent_quantity)))/(ttl_retail_price)
|
||
|
|
WHEN (parent_product_class= 'Bundle'
|
||
|
|
OR product_type_class in('Bundle','Flash Bundle'))
|
||
|
|
AND Final_discount=0
|
||
|
|
AND (parent_dynamic_price =0
|
||
|
|
OR parent_dynamic_price IS NULL)
|
||
|
|
AND A.external_id = B.external_id
|
||
|
|
AND A.parent_sku = B.parent_sku THEN (cast(final_wholesale_price AS decimal(22,2))*quantity*(ttl_wholesale_price-(parent_wholesale_price*parent_quantity)))/ttl_wholesale_price
|
||
|
|
WHEN (parent_product_class= 'Bundle'
|
||
|
|
OR product_type_class in('Bundle','Flash Bundle'))
|
||
|
|
AND Final_discount=0
|
||
|
|
AND parent_dynamic_price>0
|
||
|
|
AND A.external_id = B.external_id
|
||
|
|
AND A.parent_sku = B.parent_sku THEN (cast(final_wholesale_price AS decimal(22,2))*quantity*(ttl_wholesale_price-ttl_parent_dynamic_price))/ttl_wholesale_price
|
||
|
|
END AS additional_discount,
|
||
|
|
CASE
|
||
|
|
WHEN final_discount >0 THEN cast(final_discount AS decimal(22,2))-cast(seller_margin AS decimal(22,2))
|
||
|
|
END effective_coupon_discount
|
||
|
|
FROM public.base_netsuite_stage3 A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT external_id ,
|
||
|
|
parent_sku ,
|
||
|
|
round(sum(retail_price*quantity))ttl_retail_price,
|
||
|
|
round(sum(final_wholesale_price*quantity))ttl_wholesale_price,
|
||
|
|
min(CASE WHEN parent_dynamic_price>0 THEN parent_dynamic_price END)ttl_parent_dynamic_price
|
||
|
|
FROM public.base_netsuite_stage3
|
||
|
|
WHERE parent_product_class= 'Bundle'
|
||
|
|
OR product_type_class in('Bundle','Flash Bundle')
|
||
|
|
GROUP BY external_id ,
|
||
|
|
parent_sku) B ON A.external_id = B.external_id
|
||
|
|
AND A.parent_sku = B.parent_sku;
|
||
|
|
|
||
|
|
|
||
|
|
DELETE
|
||
|
|
FROM public.base_netsuite_final
|
||
|
|
WHERE external_id IN
|
||
|
|
(SELECT DISTINCT external_id
|
||
|
|
FROM public.order_level_data);
|
||
|
|
|
||
|
|
|
||
|
|
INSERT INTO public.base_netsuite_final
|
||
|
|
SELECT A.external_id,
|
||
|
|
A.transaction_date ,
|
||
|
|
A.discount_amount order_discount_amount,
|
||
|
|
A.shipping_cost ,
|
||
|
|
coupon_code ,
|
||
|
|
A.coupon_applied_on ,
|
||
|
|
reseller_tier_name tier,
|
||
|
|
B.sku product_sku,
|
||
|
|
B.quantity ,
|
||
|
|
retail_price,
|
||
|
|
CASE
|
||
|
|
WHEN A.external_id = C.external_id
|
||
|
|
AND B.sku = C.sku
|
||
|
|
AND B.parent_sku = C.parent_sku THEN (seller_margin+diff)/B.quantity
|
||
|
|
ELSE B.seller_margin/B.quantity
|
||
|
|
END seller_margin,
|
||
|
|
((coalesce(retail_price,0)*quantity) -(coalesce(CASE WHEN A.external_id = C.external_id
|
||
|
|
AND B.sku = C.sku
|
||
|
|
AND B.parent_sku = C.parent_sku THEN (seller_margin+diff)ELSE seller_margin END ,0)) -coalesce(CASE WHEN A.external_id= 'OD1641992277895310REG' THEN 180000 ELSE effective_coupon_discount END,0) -(coalesce(additional_discount,0)) -coalesce(loyalty_discount,0))/quantity discounted_price,
|
||
|
|
cast(final_discount AS decimal(22,2))/quantity coupon_discount,
|
||
|
|
CASE
|
||
|
|
WHEN A.external_id= 'OD1641992277895310REG' THEN 1200
|
||
|
|
ELSE effective_coupon_discount/quantity
|
||
|
|
END effective_coupon_discount ,
|
||
|
|
loyalty_discount/quantity loyalty_discount,
|
||
|
|
coalesce(additional_discount,0)/quantity additional_discount,
|
||
|
|
CASE
|
||
|
|
WHEN (parent_product_class = 'Bundle'
|
||
|
|
AND product_type_class = 'Flash')
|
||
|
|
OR product_type_class = 'Flash Bundle' THEN 'Flash Bundle'
|
||
|
|
WHEN (parent_product_class = 'Flash'
|
||
|
|
OR product_type_class = 'Flash') THEN 'Flash'
|
||
|
|
WHEN (parent_product_class = 'Bundle'
|
||
|
|
OR product_type_class = 'Bundle')THEN 'Bundle'
|
||
|
|
ELSE 'Regular'
|
||
|
|
END item_type,
|
||
|
|
diff
|
||
|
|
FROM public.order_level_data A
|
||
|
|
LEFT JOIN public.base_netsuite_stage4 B ON A.external_id = B.external_id
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT external_id ,
|
||
|
|
sku ,
|
||
|
|
parent_sku ,
|
||
|
|
seller_margin * diff/sum(seller_margin)over(partition BY external_id) diff
|
||
|
|
FROM public.base_netsuite_stage4 A
|
||
|
|
INNER JOIN public.order_check_table B ON A.external_id = B.order_id)C ON B.external_id = C.external_id
|
||
|
|
AND B.sku = C.sku
|
||
|
|
AND B.parent_sku = C.parent_sku ;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.gm_dimensions_stage1;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.gm_dimensions_stage1 AS
|
||
|
|
SELECT A.id AS external_id ,
|
||
|
|
shipping_province,
|
||
|
|
reseller_id ,
|
||
|
|
json_extract_path_text(A.reseller_info,'name',TRUE) reseller_name,
|
||
|
|
json_extract_path_text(A.reseller_info,'email',TRUE) reseller_email,
|
||
|
|
json_extract_path_text(A.reseller_info,'mobile',TRUE) reseller_mobile,
|
||
|
|
discount_type,
|
||
|
|
json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
|
||
|
|
C.sku ,
|
||
|
|
C.name sku_name,
|
||
|
|
brand_name ,
|
||
|
|
shipping_to,
|
||
|
|
product_type ,
|
||
|
|
category_name,
|
||
|
|
order_placed_by
|
||
|
|
FROM raena_order_management.order A
|
||
|
|
LEFT JOIN raena_order_management.discount_coupon B ON A.coupon_code = B.coupon_code
|
||
|
|
LEFT JOIN raena_order_management.order_item C ON A.id = C.order_id
|
||
|
|
WHERE payment_status ='Paid'
|
||
|
|
AND A.is_campaign = 'false'
|
||
|
|
AND cast(A.created_at AS date) >='2022-05-24';
|
||
|
|
|
||
|
|
|
||
|
|
DELETE
|
||
|
|
FROM public.GM_dashboard
|
||
|
|
WHERE external_id IN
|
||
|
|
(SELECT DISTINCT external_id
|
||
|
|
FROM public.gm_dimensions_stage1);
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
INSERT INTO public.GM_dashboard
|
||
|
|
SELECT A.external_id ,
|
||
|
|
B.transaction_date+interval'7 Hours' transaction_date,
|
||
|
|
B.order_discount_amount ,
|
||
|
|
B.shipping_cost ,
|
||
|
|
A.shipping_province,
|
||
|
|
B.coupon_code ,
|
||
|
|
A.discount_type ,
|
||
|
|
B.coupon_applied_on ,
|
||
|
|
A.brand_name,
|
||
|
|
A.category_name ,
|
||
|
|
A.product_type ,
|
||
|
|
A.sku sku,
|
||
|
|
A.sku_name ,
|
||
|
|
A.reseller_name ,
|
||
|
|
A.reseller_email ,
|
||
|
|
A.reseller_mobile ,
|
||
|
|
reseller_tier_name tier_name,
|
||
|
|
reseller_id ,
|
||
|
|
B.quantity ,
|
||
|
|
B.retail_price ,
|
||
|
|
B.seller_margin ,
|
||
|
|
B.discounted_price ,
|
||
|
|
B.additional_discount ,
|
||
|
|
B.loyalty_discount ,
|
||
|
|
B.effective_coupon_discount,
|
||
|
|
B.item_type ,
|
||
|
|
order_placed_by ,
|
||
|
|
min( case when A.sku = C.sku
|
||
|
|
AND cast(transaction_date AS date) = Cast(created_at AS date)
|
||
|
|
then coalesce(cogs_promo,cogs_non_promo) end) cogs,
|
||
|
|
shipping_to order_recipient,
|
||
|
|
CASE
|
||
|
|
WHEN brand_name IN ('LUXCRIME',
|
||
|
|
'SKINTIFIC',
|
||
|
|
'TRUEVE',
|
||
|
|
'SANIYE',
|
||
|
|
'BEAUDELAB',
|
||
|
|
'BRASOV',
|
||
|
|
'FACE REPUBLIC',
|
||
|
|
'SKIN 1004',
|
||
|
|
'PREMIERE BEAUTE',
|
||
|
|
'ALLURA',
|
||
|
|
'LIPLAPIN',
|
||
|
|
'ROUNDLAB',
|
||
|
|
'FACE FLUX',
|
||
|
|
'DOLLGORAE',
|
||
|
|
'SKINUA',
|
||
|
|
'PUREFORET',
|
||
|
|
'SKINTIFIC',
|
||
|
|
'OHMYSKIN',
|
||
|
|
'FEAT FOR SKIN',
|
||
|
|
'SECONDATE',
|
||
|
|
'KYND',
|
||
|
|
'PURNAMA',
|
||
|
|
'BASE',
|
||
|
|
'LAVIE LASH',
|
||
|
|
'REI SKIN',
|
||
|
|
'USTRAA',
|
||
|
|
'BRUNBRUN PARISGLOWINC',
|
||
|
|
'SOONHAN',
|
||
|
|
'THE YEON',
|
||
|
|
'MIXSOON',
|
||
|
|
'KOSE COSMEPORT') THEN 'High GM'
|
||
|
|
WHEN brand_name IN ('W DRESSROOM',
|
||
|
|
'BEAUSTA',
|
||
|
|
'Dewycel',
|
||
|
|
'GLUTANEX',
|
||
|
|
'HISTOIRE NATURELLE',
|
||
|
|
'FORENCOS',
|
||
|
|
'BELLFLOWER',
|
||
|
|
'MAXCLINIC') THEN 'EL/PL'
|
||
|
|
END brand_type ,
|
||
|
|
Customer_type,
|
||
|
|
BB.gm_target
|
||
|
|
FROM public.gm_dimensions_stage1 A
|
||
|
|
INNER JOIN public.base_netsuite_final B ON A.external_id =B.external_id
|
||
|
|
AND A.sku=B.product_sku
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT sku ,
|
||
|
|
min(cogs_non_promo) cogs_non_promo,
|
||
|
|
min(cogs_promo) cogs_promo,
|
||
|
|
--cogs ,
|
||
|
|
--sku_cogs_type ,
|
||
|
|
Cast(created_at AS date) created_at
|
||
|
|
FROM public.sku_cogs_audit
|
||
|
|
group by sku ,
|
||
|
|
Cast(created_at AS date) ) C ON A.sku = C.sku
|
||
|
|
AND cast(transaction_date AS date) = Cast(created_at AS date)
|
||
|
|
left join (SELECT DISTINCT id order_id,
|
||
|
|
CASE
|
||
|
|
WHEN customer_id IS NOT NULL
|
||
|
|
AND order_placed_by = 'admin' THEN 'Offline Dropshipper'
|
||
|
|
WHEN customer_id IS NOT NULL
|
||
|
|
AND order_placed_by <> 'admin' THEN 'Online Dropshipper'
|
||
|
|
WHEN customer_id IS NULL
|
||
|
|
AND order_placed_by <> 'admin' THEN 'Online Reseller'
|
||
|
|
WHEN customer_id IS NULL
|
||
|
|
AND order_placed_by = 'admin' THEN 'Offline Reseller'
|
||
|
|
END Customer_type
|
||
|
|
FROM raena_order_management.order) AA on A.external_id = AA.order_id
|
||
|
|
left join (
|
||
|
|
select DISTINCT SKU,gm_target,t.name as tierName
|
||
|
|
from raena_gross_margin_management.gross_margin_config AA
|
||
|
|
LEFT JOIN raena_user_management.tier t
|
||
|
|
ON cast(AA.gm_target_tier as TEXT) =cast(t.id as TEXT)
|
||
|
|
) BB
|
||
|
|
on A.sku = BB.sku
|
||
|
|
and A.reseller_tier_name =BB.tierName
|
||
|
|
GROUP BY A.external_id ,
|
||
|
|
B.transaction_date+interval'7 Hours' ,
|
||
|
|
B.order_discount_amount ,
|
||
|
|
B.shipping_cost ,
|
||
|
|
A.shipping_province,
|
||
|
|
B.coupon_code ,
|
||
|
|
A.discount_type ,
|
||
|
|
B.coupon_applied_on ,
|
||
|
|
A.brand_name,
|
||
|
|
A.category_name ,
|
||
|
|
A.product_type ,
|
||
|
|
A.sku ,
|
||
|
|
A.sku_name ,
|
||
|
|
A.reseller_name ,
|
||
|
|
A.reseller_email ,
|
||
|
|
A.reseller_mobile ,
|
||
|
|
reseller_tier_name ,
|
||
|
|
reseller_id ,
|
||
|
|
B.quantity ,
|
||
|
|
B.retail_price ,
|
||
|
|
B.seller_margin ,
|
||
|
|
B.discounted_price ,
|
||
|
|
B.additional_discount ,
|
||
|
|
B.loyalty_discount ,
|
||
|
|
B.effective_coupon_discount,
|
||
|
|
B.item_type ,
|
||
|
|
order_placed_by,
|
||
|
|
shipping_to,
|
||
|
|
CASE
|
||
|
|
WHEN brand_name IN ('LUXCRIME',
|
||
|
|
'SKINTIFIC',
|
||
|
|
'TRUEVE',
|
||
|
|
'SANIYE',
|
||
|
|
'BEAUDELAB',
|
||
|
|
'BRASOV',
|
||
|
|
'FACE REPUBLIC',
|
||
|
|
'SKIN 1004',
|
||
|
|
'PREMIERE BEAUTE',
|
||
|
|
'ALLURA',
|
||
|
|
'LIPLAPIN',
|
||
|
|
'ROUNDLAB',
|
||
|
|
'FACE FLUX',
|
||
|
|
'DOLLGORAE',
|
||
|
|
'SKINUA',
|
||
|
|
'PUREFORET',
|
||
|
|
'SKINTIFIC',
|
||
|
|
'OHMYSKIN',
|
||
|
|
'FEAT FOR SKIN',
|
||
|
|
'SECONDATE',
|
||
|
|
'KYND',
|
||
|
|
'PURNAMA',
|
||
|
|
'BASE',
|
||
|
|
'LAVIE LASH',
|
||
|
|
'REI SKIN',
|
||
|
|
'USTRAA',
|
||
|
|
'BRUNBRUN PARISGLOWINC',
|
||
|
|
'SOONHAN',
|
||
|
|
'THE YEON',
|
||
|
|
'MIXSOON',
|
||
|
|
'KOSE COSMEPORT') THEN 'High GM'
|
||
|
|
WHEN brand_name IN ('W DRESSROOM',
|
||
|
|
'BEAUSTA',
|
||
|
|
'Dewycel',
|
||
|
|
'GLUTANEX',
|
||
|
|
'HISTOIRE NATURELLE',
|
||
|
|
'FORENCOS',
|
||
|
|
'BELLFLOWER',
|
||
|
|
'MAXCLINIC') THEN 'EL/PL'
|
||
|
|
END,Customer_type,
|
||
|
|
BB.gm_target ;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.business_dimensions_stage1;
|
||
|
|
|
||
|
|
CREATE TABLE public.business_dimensions_stage1 AS
|
||
|
|
SELECT A.id AS external_id ,
|
||
|
|
reseller_id,
|
||
|
|
customer_id,
|
||
|
|
cart_id,
|
||
|
|
A.flash_sale_id,
|
||
|
|
shipping_to,
|
||
|
|
payment_status,
|
||
|
|
A.status,
|
||
|
|
A.stock_type,
|
||
|
|
order_placed_by,
|
||
|
|
order_warehouse,
|
||
|
|
cast(channel_id as text) channel_id ,
|
||
|
|
medium,
|
||
|
|
marketplace,
|
||
|
|
provider,
|
||
|
|
coalesce(cast(B.tier_id as varchar),cast(C.tier_id as varchar)) tier_id ,
|
||
|
|
json_extract_path_text(A.reseller_info,'city',TRUE) reseller_city,
|
||
|
|
json_extract_path_text(A.reseller_info,'name',TRUE) reseller_name,
|
||
|
|
json_extract_path_text(A.reseller_info,'email',TRUE) reseller_email,
|
||
|
|
json_extract_path_text(A.reseller_info,'mobile',TRUE) reseller_mobile,
|
||
|
|
json_extract_path_text(A.reseller_info,'store',TRUE) reseller_store,
|
||
|
|
json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
|
||
|
|
shipping_address_line1 ,
|
||
|
|
shipping_address_line2 ,
|
||
|
|
shipping_pincode ,
|
||
|
|
shipping_district,
|
||
|
|
shipping_city ,
|
||
|
|
shipping_province ,
|
||
|
|
is_bank_transfer ,
|
||
|
|
is_campaign ,
|
||
|
|
discount_type
|
||
|
|
FROM raena_order_management.order A
|
||
|
|
left join public.tier_name B on A.id = B.id
|
||
|
|
left join raena_user_management.user C on cast(A.reseller_id as varchar) = cast(C.id as varchar)
|
||
|
|
left join raena_order_management.discount_coupon D on A.coupon_code = D.coupon_code
|
||
|
|
where cast(A.created_at AS date) >='2022-05-24';
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DELETE
|
||
|
|
FROM public.business_report
|
||
|
|
WHERE external_id IN
|
||
|
|
(SELECT DISTINCT external_id
|
||
|
|
FROM public.business_dimensions_stage1);
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
Insert into public.business_report
|
||
|
|
SELECT A.external_id,
|
||
|
|
cast(B.transaction_date as date) created_date ,
|
||
|
|
B.order_discount_amount,
|
||
|
|
B.shipping_cost ,
|
||
|
|
coupon_code ,
|
||
|
|
D.name brand_name,
|
||
|
|
C.product_type,
|
||
|
|
E.name category_name,
|
||
|
|
C.name sku_name,
|
||
|
|
B.product_sku sku,
|
||
|
|
reseller_id,
|
||
|
|
customer_id,
|
||
|
|
cart_id,
|
||
|
|
A.flash_sale_id,
|
||
|
|
shipping_to,
|
||
|
|
payment_status,
|
||
|
|
status,
|
||
|
|
A.stock_type,
|
||
|
|
order_placed_by,
|
||
|
|
order_warehouse,
|
||
|
|
channel_id,
|
||
|
|
medium,
|
||
|
|
marketplace,
|
||
|
|
provider,
|
||
|
|
reseller_tier_name tier,
|
||
|
|
reseller_city,
|
||
|
|
reseller_name,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
reseller_store,
|
||
|
|
shipping_address_line1 ,
|
||
|
|
shipping_address_line2 ,
|
||
|
|
shipping_pincode ,
|
||
|
|
shipping_district,
|
||
|
|
shipping_city ,
|
||
|
|
shipping_province ,
|
||
|
|
is_bank_transfer ,
|
||
|
|
is_campaign ,
|
||
|
|
C.Country,
|
||
|
|
B.quantity ,
|
||
|
|
B.retail_price,
|
||
|
|
seller_margin,
|
||
|
|
discounted_price,
|
||
|
|
loyalty_discount,
|
||
|
|
effective_coupon_discount ,
|
||
|
|
additional_discount,
|
||
|
|
item_type
|
||
|
|
FROM public.business_dimensions_stage1 A
|
||
|
|
LEFT JOIN public.base_netsuite_final B ON A.external_id = B.external_id
|
||
|
|
left join raena_catalog_management.product C on B.product_sku = C.sku
|
||
|
|
left join raena_catalog_management.brand D on C.brand_id = D.id
|
||
|
|
left join raena_catalog_management.category E on C.category_id = E.id;
|
||
|
|
|
||
|
|
DELETE
|
||
|
|
FROM public.OM_GM_DB_Product_category
|
||
|
|
WHERE external_Id IN
|
||
|
|
(SELECT DISTINCT external_id
|
||
|
|
FROM public.GM_dashboard
|
||
|
|
WHERE transaction_date >='2022-05-24');
|
||
|
|
|
||
|
|
INSERT INTO OM_GM_DB_Product_category
|
||
|
|
SELECT A.external_id ,
|
||
|
|
transaction_date,
|
||
|
|
concat(left(TO_char(transaction_date,'month'),3),date_part('year',transaction_date)) AS Time,
|
||
|
|
order_discount_amount ,
|
||
|
|
shipping_cost ,
|
||
|
|
coupon_code ,
|
||
|
|
discount_type ,
|
||
|
|
coupon_applied_on ,
|
||
|
|
D.brand_name,
|
||
|
|
D.category_name ,
|
||
|
|
D.product_type ,
|
||
|
|
A.sku,
|
||
|
|
D.name sku_name ,
|
||
|
|
reseller_name ,
|
||
|
|
reseller_email ,
|
||
|
|
reseller_mobile ,
|
||
|
|
tier_name ,
|
||
|
|
reseller_id ,
|
||
|
|
quantity ,
|
||
|
|
retail_price ,
|
||
|
|
seller_margin ,
|
||
|
|
discounted_price ,
|
||
|
|
additional_discount,
|
||
|
|
item_type ,
|
||
|
|
order_placed_by ,
|
||
|
|
A.cogs,
|
||
|
|
shipping_province ,
|
||
|
|
CASE
|
||
|
|
WHEN A.external_id = C.external_id THEN 'Yes'
|
||
|
|
ELSE 'No'
|
||
|
|
END Flag ,
|
||
|
|
CASE
|
||
|
|
WHEN tier_name='GOLD' THEN gold_price
|
||
|
|
WHEN tier_name ='SILVER' THEN silver_price
|
||
|
|
WHEN tier_name ='BRONZE' THEN bronze_price
|
||
|
|
ELSE retail_price-seller_margin
|
||
|
|
END wholesale_price,
|
||
|
|
order_recipient,
|
||
|
|
brand_type,
|
||
|
|
Customer_type,gm_target
|
||
|
|
FROM public.GM_dashboard A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT DISTINCT external_id
|
||
|
|
FROM
|
||
|
|
(SELECT external_id,
|
||
|
|
discounted_price ,
|
||
|
|
sku ,
|
||
|
|
(((retail_price-seller_margin)*quantity) -(cogs*quantity))/((retail_price-seller_margin)*quantity) Pre_discount,
|
||
|
|
CASE WHEN discounted_price>0 THEN ((discounted_price*quantity) -(cogs*quantity))/(discounted_price*quantity) ELSE 0 END Post_discount
|
||
|
|
FROM public.GM_dashboard) A
|
||
|
|
WHERE Post_discount>Pre_discount) C ON A.external_id = C.external_id
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT DISTINCT Sku ,
|
||
|
|
A.name,
|
||
|
|
A.product_type,
|
||
|
|
C.name category_name ,
|
||
|
|
B.name brand_name
|
||
|
|
FROM raena_catalog_management.product A
|
||
|
|
LEFT JOIN raena_catalog_management.brand B ON A.brand_id = B.id
|
||
|
|
LEFT JOIN raena_catalog_management.category c ON A.category_id=C.id) D ON A.sku = D.sku
|
||
|
|
LEFT JOIN pricing_sheet P ON A.sku=p.skucode
|
||
|
|
WHERE A.cogs IS NOT NULL
|
||
|
|
AND transaction_date >='2022-05-24';
|
||
|
|
|
||
|
|
--Business Dashboard
|
||
|
|
DELETE
|
||
|
|
FROM public.GM_GROWTH_TAB1
|
||
|
|
WHERE created_date >='2022-05-24';
|
||
|
|
|
||
|
|
INSERT INTO public.GM_GROWTH_TAB1
|
||
|
|
SELECT created_date,
|
||
|
|
brand_name,
|
||
|
|
product_type,
|
||
|
|
category_name,
|
||
|
|
sku,
|
||
|
|
sku_name,
|
||
|
|
order_placed_by,
|
||
|
|
shipping_province,
|
||
|
|
shipping_to,
|
||
|
|
status,
|
||
|
|
channel_name,
|
||
|
|
reseller_name,reseller_email,
|
||
|
|
count(DISTINCT external_id) AS Number_of_Orders ,
|
||
|
|
sum(retail_price*quantity) RSP ,
|
||
|
|
sum((retail_price*quantity)-(seller_margin*quantity))wholesale_price ,
|
||
|
|
sum(discounted_price*quantity) AS Payment_Price ,
|
||
|
|
sum(effective_coupon_discount*quantity) AS Effective_Coupon_Discount,
|
||
|
|
sum(loyalty_discount*quantity)Total_Loyalty_point,
|
||
|
|
sum(additional_discount*quantity)additional_discount,
|
||
|
|
sum(seller_margin*quantity)seller_margin,
|
||
|
|
sum(discounted_price*quantity)/count(DISTINCT external_id) AS AOV,
|
||
|
|
sum(quantity) AS quantity
|
||
|
|
FROM
|
||
|
|
(SELECT DISTINCT a.created_date,
|
||
|
|
'All' AS brand_name,
|
||
|
|
'All' AS product_type,
|
||
|
|
'All' AS category_name,
|
||
|
|
'All' AS sku,
|
||
|
|
'All' AS sku_name,
|
||
|
|
order_placed_by,
|
||
|
|
shipping_province,
|
||
|
|
shipping_to,
|
||
|
|
status,
|
||
|
|
b.channel_name,
|
||
|
|
reseller_name,reseller_email,
|
||
|
|
external_id,
|
||
|
|
retail_price,
|
||
|
|
quantity,
|
||
|
|
seller_margin,
|
||
|
|
discounted_price,
|
||
|
|
effective_coupon_discount,
|
||
|
|
loyalty_discount,
|
||
|
|
additional_discount,
|
||
|
|
row_number() over (partition BY external_id
|
||
|
|
ORDER BY external_id) AS R
|
||
|
|
FROM public.business_report a
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT DISTINCT A.id ,
|
||
|
|
channel_id ,
|
||
|
|
D.name channel_name
|
||
|
|
FROM raena_order_management.order A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT id,
|
||
|
|
name
|
||
|
|
FROM raena_order_management.channel) D ON A.channel_id = D.id
|
||
|
|
WHERE cast(created_at AS date) >= '2022-05-24'
|
||
|
|
AND channel_id IS NOT NULL) b ON a.external_id=b.id
|
||
|
|
WHERE cast(is_campaign AS varchar)='false'
|
||
|
|
AND payment_status='Paid'
|
||
|
|
AND created_date >= '2022-05-24'
|
||
|
|
UNION SELECT DISTINCT a.created_date,
|
||
|
|
brand_name,
|
||
|
|
product_type,
|
||
|
|
category_name,
|
||
|
|
sku,
|
||
|
|
sku_name,
|
||
|
|
order_placed_by,
|
||
|
|
shipping_province,
|
||
|
|
shipping_to,
|
||
|
|
status,
|
||
|
|
b.channel_name,
|
||
|
|
reseller_name,reseller_email,
|
||
|
|
external_id,
|
||
|
|
retail_price,
|
||
|
|
quantity,
|
||
|
|
seller_margin,
|
||
|
|
discounted_price,
|
||
|
|
effective_coupon_discount,
|
||
|
|
loyalty_discount,
|
||
|
|
additional_discount,
|
||
|
|
row_number() over (partition BY external_id
|
||
|
|
ORDER BY external_id) AS R
|
||
|
|
FROM public.business_report a
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT DISTINCT A.id ,
|
||
|
|
channel_id ,
|
||
|
|
D.name channel_name
|
||
|
|
FROM raena_order_management.order A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT id,
|
||
|
|
name
|
||
|
|
FROM raena_order_management.channel) D ON A.channel_id = D.id
|
||
|
|
WHERE cast(created_at AS date) >= '2022-05-24'
|
||
|
|
AND channel_id IS NOT NULL) b ON a.external_id=b.id
|
||
|
|
WHERE cast(is_campaign AS varchar)='false'
|
||
|
|
AND payment_status='Paid'
|
||
|
|
AND created_date >= '2022-05-24')
|
||
|
|
GROUP BY created_date,
|
||
|
|
brand_name,
|
||
|
|
product_type,
|
||
|
|
category_name,
|
||
|
|
sku,
|
||
|
|
sku_name,
|
||
|
|
order_placed_by,
|
||
|
|
shipping_province,
|
||
|
|
shipping_to,
|
||
|
|
status,
|
||
|
|
channel_name,
|
||
|
|
reseller_name,reseller_email;
|
||
|
|
|
||
|
|
--Metric Trend
|
||
|
|
DROP TABLE IF EXISTS public.GM_GROWTH_TAB2;
|
||
|
|
CREATE TABLE public.GM_GROWTH_TAB2 AS
|
||
|
|
SELECT 'year' AS frequency,
|
||
|
|
cast(date_part('year',created_date) AS varchar) AS time,
|
||
|
|
cast(date_part('year',created_date) AS int) AS sort ,
|
||
|
|
*
|
||
|
|
FROM public.GM_GROWTH_TAB1
|
||
|
|
UNION
|
||
|
|
SELECT 'quarter' AS frequency,
|
||
|
|
To_char(created_date,'quarter') AS month_name,
|
||
|
|
date_part('quarter',created_date) as sort ,
|
||
|
|
*
|
||
|
|
FROM public.GM_GROWTH_TAB1
|
||
|
|
UNION
|
||
|
|
SELECT 'month' AS frequency,
|
||
|
|
To_char(created_date,'month') AS month_name,
|
||
|
|
date_part('month',created_date) as sort ,
|
||
|
|
*
|
||
|
|
FROM public.GM_GROWTH_TAB1
|
||
|
|
UNION
|
||
|
|
SELECT 'week' AS frequency,
|
||
|
|
To_char(created_date,'week') AS month_name,
|
||
|
|
cast(left(To_char(created_date,'week'),1) as int) AS sort ,
|
||
|
|
*
|
||
|
|
FROM public.GM_GROWTH_TAB1
|
||
|
|
UNION
|
||
|
|
SELECT 'day' AS frequency,
|
||
|
|
cast(date_part('day',created_date) as varchar) AS month_name,
|
||
|
|
cast(date_part('day',created_date) AS int) AS sort ,
|
||
|
|
*
|
||
|
|
FROM public.GM_GROWTH_TAB1
|
||
|
|
ORDER BY 3;
|
||
|
|
|
||
|
|
|