2324 lines
89 KiB
Bash
2324 lines
89 KiB
Bash
#!/bin/bash
|
|
|
|
|
|
echo -e " \n----------- ACCEPTING NUMBER OF DAYS BEFORE THE RUN DATE FOR WHICH THE REPORT IS TO BE RUN --------------\n"
|
|
|
|
backDay=$1
|
|
|
|
echo $backDay
|
|
|
|
echo -e " \n------------- DATE IN THE REQUIRED FORMAT --------------\n"
|
|
reportDate=$(date -d"$backDay day ago" "+%Y-%m-%d")
|
|
date
|
|
echo 'reportDate'=$reportDate
|
|
|
|
|
|
echo "
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.manual_bundle_sku_data ;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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(cast(C.tier_price::json->'07030fbe-5801-4318-9e97-fe33fa169894' as varchar) AS decimal(22,2)) bronze_price,
|
|
cast(cast(C.tier_price::json->'8eb95d6e-915a-4a91-9c12-fa43db995e19' as varchar) AS decimal(22,2)) silver_price,
|
|
cast(cast(C.tier_price::json->'bf645e97-8a48-4977-8367-e987489760f9' as varchar) AS decimal(22,2)) gold_price,
|
|
cast(C.retail_price AS decimal(22,2)) child_retail_price
|
|
FROM raena_analytics.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 raena_analytics.order_level_data;
|
|
|
|
|
|
CREATE TABLE raena_analytics.order_level_data AS
|
|
SELECT DISTINCT external_id,
|
|
transaction_date+interval'7 hours' AS transaction_date,
|
|
discount_amount,
|
|
shipping_cost,
|
|
A.coupon_code,
|
|
replace(cast(reseller_tier_name as varchar),'\"','')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,
|
|
is_campaign,
|
|
order_placed_by
|
|
FROM
|
|
(SELECT A.id AS external_id ,
|
|
(A.created_at) AS transaction_date ,
|
|
A.discount_amount,
|
|
applied_shipping_amount shipping_cost,
|
|
A.coupon_code,
|
|
A.reseller_info::json->'tierName' 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,
|
|
cast(is_campaign as varchar) is_campaign,
|
|
order_placed_by
|
|
FROM raena_order_management.order A
|
|
WHERE ( payment_status='Paid' or id like 'PL%')
|
|
AND cast(A.created_at+interval '7 hours' AS date) >='$reportDate' ) A
|
|
LEFT JOIN raena_order_management.discount_coupon C ON A.coupon_code = C.coupon_code;
|
|
|
|
|
|
SELECT max(created_at)
|
|
FROM raena_order_management.order ;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.base_netsuite_stage1_V1;
|
|
|
|
|
|
CREATE TABLE raena_analytics.base_netsuite_stage1_V1 AS
|
|
SELECT DISTINCT transaction_date transaction_date,
|
|
A.external_id,
|
|
CASE
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_sku
|
|
WHEN B.id = F.sales_sub_order_id THEN F.sku
|
|
ELSE B.parent_sku
|
|
END sku,
|
|
B.parent_sku parent_sku,
|
|
CASE
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_quantity*B.quantity
|
|
WHEN B.id = F.sales_sub_order_id THEN F.quantity
|
|
ELSE B.quantity
|
|
END quantity,
|
|
B.quantity parent_quantity,
|
|
CASE
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_retail_price
|
|
WHEN B.id = F.sales_sub_order_id THEN F.retail_price
|
|
ELSE B.retail_price
|
|
END retail_price,
|
|
B.retail_price parent_retail_price,
|
|
CASE
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
|
|
AND reseller_tier_name = 'BRONZE' THEN bronze_price
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
|
|
AND reseller_tier_name = 'SILVER' THEN silver_price
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
|
|
AND reseller_tier_name = 'GOLD' THEN gold_price
|
|
WHEN B.id = F.sales_sub_order_id THEN F.effective_wholesale_price
|
|
ELSE B.effective_wholesale_price
|
|
END effective_wholesale_price,
|
|
B.effective_wholesale_price parent_wholesale_price,
|
|
CASE
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
|
|
AND reseller_tier_name = 'BRONZE' THEN bronze_price
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
|
|
AND reseller_tier_name = 'SILVER' THEN silver_price
|
|
WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
|
|
AND reseller_tier_name = 'GOLD' THEN gold_price
|
|
WHEN B.id = F.sales_sub_order_id THEN F.actual_wholesale_price
|
|
ELSE B.actual_wholesale_price
|
|
END actual_wholesale_price,
|
|
B.actual_wholesale_price parent_actual_wholesale_price,
|
|
CASE
|
|
WHEN B.id = F.sales_sub_order_id THEN F.coupon_discount
|
|
ELSE B.coupon_discount
|
|
END discount_price,
|
|
CASE
|
|
WHEN discount_amount > 0
|
|
AND A.coupon_code IS NULL
|
|
AND A.order_placed_by = 'admin'
|
|
AND coupon_applied_on IS NULL THEN 'Cart'
|
|
ELSE coupon_applied_on
|
|
END coupon_applied_on,
|
|
A.discount_amount,
|
|
0 Dynamic_price ,
|
|
0 parent_dynamic_price,
|
|
CASE
|
|
WHEN B.id = F.sales_sub_order_id THEN F.payment_amount
|
|
ELSE B.payment_amount
|
|
END payment_price,
|
|
A.payment_amount,
|
|
B.product_class,
|
|
coalesce(F.product_class,B.product_class) parent_product_class,
|
|
CASE
|
|
WHEN A.product_type = 'Flash'
|
|
AND B.parent_sku LIKE 'BAZ%' THEN 'Flash Bundle'
|
|
WHEN A.product_type = 'Flash'
|
|
AND B.parent_sku NOT LIKE 'BAZ%' THEN 'Flash'
|
|
WHEN B.parent_sku LIKE 'BAZ%' THEN 'Bundle'
|
|
ELSE 'Regular'
|
|
END AS product_type_class,
|
|
B.id sales_sub_order_id,
|
|
A.order_loyalty_discount,
|
|
B.loyalty_discount,
|
|
reseller_tier_name,
|
|
price_type,
|
|
a.is_campaign,
|
|
B.cogs_discount_type
|
|
FROM raena_analytics.order_level_data A
|
|
LEFT JOIN raena_order_management.sales_sub_order B ON A.external_id = B.order_id and status <> 'Cancelled'
|
|
LEFT JOIN raena_analytics.manual_bundle_sku_data D ON cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
|
|
LEFT JOIN
|
|
(SELECT id ,
|
|
sales_sub_order_id ,
|
|
retail_price,
|
|
Sku ,
|
|
quantity,
|
|
effective_wholesale_price,
|
|
actual_wholesale_price,
|
|
coupon_discount,
|
|
payment_amount,
|
|
product_class,
|
|
loyalty_discount
|
|
FROM raena_order_management.sales_sub_order_parent_child ssopc
|
|
WHERE product_class = 'Bundle'
|
|
AND cast(created_at+interval'7 hours' AS date)>='$reportDate') F ON B.id = F.sales_sub_order_id
|
|
ORDER BY 1,
|
|
2;
|
|
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.base_netsuite_stage2_v1_base ;
|
|
|
|
|
|
CREATE TABLE raena_analytics.base_netsuite_stage2_v1_base AS
|
|
SELECT transaction_date,
|
|
A.external_id,
|
|
A.sku,
|
|
parent_sku,
|
|
quantity,
|
|
parent_quantity,
|
|
retail_price,
|
|
parent_retail_price,
|
|
effective_wholesale_price,
|
|
parent_wholesale_price,
|
|
actual_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,
|
|
sales_sub_order_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,
|
|
price_type,
|
|
is_campaign,
|
|
coalesce(C.applied_cogs,D.cogs-coalesce(promo,0)) cogs,
|
|
coalesce(cogs_type,CASE WHEN A.sku = D.sku
|
|
AND A.transaction_date::date BETWEEN D.created_at AND last_date
|
|
AND promo IS NOT NULL THEN 'Promo' ELSE 'Non-Promo' END) cogs_flag,
|
|
cogs_discount_type
|
|
FROM raena_analytics.base_netsuite_stage1_V1 A
|
|
LEFT JOIN
|
|
(SELECT external_id ,
|
|
sku ,
|
|
final_loyalty_point
|
|
FROM raena_analytics.final_loyalty_point) B ON A.external_id = B.external_id
|
|
AND A.sku = B.sku
|
|
LEFT JOIN
|
|
(SELECT B.order_id ,
|
|
A.sku ,
|
|
sum(A.applied_cogs*(case when quantity>0 then quantity else 1 end))/sum(case when quantity>0 then quantity else 1 end ) applied_cogs,
|
|
A.cogs_type
|
|
FROM raena_order_management.fulfillment_detail A
|
|
LEFT JOIN raena_order_management.sales_sub_order_shipment B ON A.shipment_id = B.id
|
|
WHERE applied_cogs IS NOT null and applied_cogs <>0
|
|
group by 1,2,4) C ON A.sku = C.sku
|
|
AND A.external_id = C.order_id
|
|
LEFT JOIN
|
|
(SELECT sku,
|
|
cogs,
|
|
promo,
|
|
(created_at+interval'7 hours')::date created_at,
|
|
coalesce(lead((created_at+interval'7 hours')::date-interval'1 day' , 1) over (partition BY sku
|
|
ORDER BY sku,created_at),((CURRENT_DATE+interval'7 hours')+interval'1 day'))::date last_date
|
|
FROM raena_catalog_management.cogs_audit ca
|
|
ORDER BY 1 ,
|
|
4) D ON A.sku = D.sku
|
|
AND A.transaction_date::date BETWEEN D.created_at AND last_date;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.base_netsuite_stage2_v1;
|
|
|
|
|
|
CREATE TABLE raena_analytics.base_netsuite_stage2_v1 AS
|
|
SELECT transaction_date,
|
|
A.external_id,
|
|
A.sku,
|
|
parent_sku,
|
|
quantity quantity,
|
|
parent_quantity,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE retail_price
|
|
END retail_price,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE parent_retail_price
|
|
END parent_retail_price,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE effective_wholesale_price
|
|
END effective_wholesale_price,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE parent_wholesale_price
|
|
END parent_wholesale_price,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE actual_wholesale_price
|
|
END actual_wholesale_price,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE discount_price
|
|
END discount_price,
|
|
coupon_applied_on,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE discount_amount
|
|
END discount_amount,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE dynamic_price
|
|
END dynamic_price,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE parent_dynamic_price
|
|
END parent_dynamic_price,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE payment_price
|
|
END payment_price,
|
|
CASE
|
|
WHEN cogs = 0
|
|
OR cogs IS NULL
|
|
OR A.sku = D.sku THEN 0
|
|
ELSE payment_amount
|
|
END payment_amount,
|
|
product_class,
|
|
parent_product_class,
|
|
product_type_class,
|
|
sales_sub_order_id,
|
|
loyalty_discount,
|
|
price_type,
|
|
is_campaign,
|
|
CASE
|
|
WHEN A.sku = D.sku THEN 0
|
|
ELSE cogs
|
|
END cogs,
|
|
cogs_flag,
|
|
cogs_discount_type
|
|
FROM raena_analytics.base_netsuite_stage2_v1_base A
|
|
LEFT JOIN
|
|
(SELECT DISTINCT sku
|
|
FROM raena_catalog_management.product
|
|
WHERE lower(name) LIKE '%not for sale%'
|
|
OR lower(name) LIKE '%free item%'
|
|
OR lower(name) LIKE '%free gift%') D ON A.sku = D.sku ;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.base_netsuite_stage3_v1 ;
|
|
|
|
|
|
CREATE TABLE raena_analytics.base_netsuite_stage3_v1 AS
|
|
SELECT A.* ,
|
|
CASE
|
|
WHEN dynamic_price>0 THEN dynamic_price
|
|
ELSE effective_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
|
|
AND retail_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 discount_price
|
|
WHEN coupon_applied_on ='Cart'
|
|
AND discount_price=0 THEN retail_price*quantity*(discount_amount) /sum(CASE WHEN retail_price<>0 THEN retail_price*quantity END)over(partition BY A.external_id)
|
|
END AS Final_discount ,
|
|
retail_price*quantity-coalesce(actual_wholesale_price,effective_wholesale_price)*quantity AS seller_margin,
|
|
CASE
|
|
WHEN product_type_class IN ('Flash Bundle',
|
|
'Flash') THEN 'Flash'
|
|
WHEN coupon_applied_on ='Cart'
|
|
AND price_type ='NEGOTIATED_PRICE' THEN 'Negotiated'
|
|
WHEN coupon_applied_on = 'Shipping Fee' THEN 'Shipping'
|
|
WHEN coupon_applied_on IS NOT NULL THEN 'Brand'
|
|
END discount_type
|
|
FROM raena_analytics.base_netsuite_stage2_v1 A ;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.base_netsuite_stage4_v1 ;
|
|
|
|
|
|
CREATE TABLE raena_analytics.base_netsuite_stage4_v1 AS
|
|
SELECT A.* ,
|
|
CASE
|
|
WHEN (parent_product_class= 'Bundle'
|
|
OR product_type_class IN('Bundle', 'Flash Bundle'))
|
|
AND A.external_id = B.external_id
|
|
AND A.parent_sku = B.parent_sku and ttl_wholesale_price<>0 THEN (effective_wholesale_price*quantity)-((cast(final_wholesale_price AS decimal(22,2))*quantity*(parent_wholesale_price*parent_quantity))/ttl_wholesale_price)
|
|
END AS additional_discount,
|
|
CASE
|
|
WHEN final_discount<>0 THEN final_discount-seller_margin
|
|
END AS effective_coupon_discount
|
|
FROM raena_analytics.base_netsuite_stage3_v1 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
|
|
FROM raena_analytics.base_netsuite_stage3_v1
|
|
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 raena_analytics.base_netsuite_final
|
|
WHERE external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.order_level_data);
|
|
|
|
|
|
INSERT INTO raena_analytics.base_netsuite_final
|
|
SELECT A.external_id,
|
|
A.transaction_date 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,
|
|
B.seller_margin/B.quantity seller_margin,
|
|
((coalesce(retail_price,0)*quantity) -coalesce(seller_margin,0) -coalesce(effective_coupon_discount,0) -coalesce((CASE WHEN final_discount >0 THEN 0 ELSE actual_wholesale_price*quantity - effective_wholesale_price*quantity END),0) -(coalesce(additional_discount,0)) -coalesce(loyalty_discount,0))/quantity discounted_price,
|
|
cast(final_discount AS decimal(22,2))/quantity coupon_discount,
|
|
effective_coupon_discount/quantity 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,
|
|
0 diff,
|
|
CASE
|
|
WHEN price_type IS NOT NULL THEN price_type
|
|
ELSE discount_type
|
|
END discount_type ,
|
|
CASE
|
|
WHEN final_discount >0 THEN 0
|
|
ELSE actual_wholesale_price*quantity - effective_wholesale_price*quantity
|
|
END promo_amount,
|
|
actual_wholesale_price,
|
|
effective_wholesale_price,
|
|
A.is_campaign,
|
|
'' sales_sub_order_id,
|
|
cogs_discount_type,
|
|
parent_sku
|
|
FROM raena_analytics.order_level_data A
|
|
LEFT JOIN raena_analytics.base_netsuite_stage4_v1 B ON A.external_id = B.external_id;
|
|
|
|
|
|
|
|
UPDATE raena_analytics.base_netsuite_final AA
|
|
SET discounted_price = new_discounted_price
|
|
from (SELECT external_id ,
|
|
product_sku ,
|
|
cast(settlement_amount/quantity AS decimal(22,2)) new_discounted_price
|
|
FROM
|
|
(SELECT A.*,
|
|
discounted_price ,
|
|
(effective_wholesale_price*B.\"Settlement amount\")/sum(effective_wholesale_price) over (partition BY external_id) settlement_amount ,
|
|
B.\"Settlement amount\"
|
|
FROM raena_analytics.base_netsuite_final A
|
|
INNER JOIN (select distinct * from raena_analytics.mp_orders_hn_wd) B ON external_id= B.\"Order ID \"
|
|
WHERE A.transaction_date >'2022-12-31' and effective_wholesale_price<>0 ) A) BB
|
|
where AA.external_id = BB.external_id
|
|
AND AA.product_sku = BB.product_sku
|
|
and AA.transaction_date >'2022-12-31';
|
|
|
|
|
|
update raena_analytics.base_netsuite_final A
|
|
set discounted_price = B.payment_price/quantity
|
|
from (select order_id,sku, max(payment_price) payment_price from raena_analytics.order_data_update group by 1,2) B
|
|
where A.external_id = B.order_id and A.parent_sku = B.sku and product_sku = parent_sku ;
|
|
|
|
|
|
update raena_analytics.base_netsuite_final A
|
|
set discounted_price = new_payment_price/A.quantity
|
|
from raena_analytics.import_data_for_sku_level_payment_amount B
|
|
where A.external_id = B.order_id and A.parent_sku = B.sku and A.product_sku <> A.parent_sku;
|
|
|
|
SELECT max(transaction_date) Netsuite_base_date
|
|
FROM raena_analytics.base_netsuite_final;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.gm_dimensions_stage1;
|
|
|
|
|
|
CREATE TABLE raena_analytics.gm_dimensions_stage1 AS
|
|
SELECT distinct A.id AS external_id ,
|
|
shipping_province,
|
|
reseller_id ,
|
|
replace(cast(A.reseller_info::json ->'name' AS varchar),'\"','') reseller_name,
|
|
replace(cast(A.reseller_info::json ->'email' AS varchar),'\"','')reseller_email,
|
|
replace(cast(A.reseller_info::json ->'mobile' AS varchar),'\"','') reseller_mobile,
|
|
discount_type,
|
|
replace(cast(A.reseller_info::json ->'tierName' AS varchar),'\"','') reseller_tier_name,
|
|
shipping_to,
|
|
CASE
|
|
WHEN provider='dsf' THEN 'DSF'
|
|
WHEN provider='rdash360' THEN 'RDash'
|
|
WHEN order_placed_by='merchant' THEN 'Merchant'
|
|
WHEN order_placed_by='admin' THEN 'Admin Panel'
|
|
WHEN order_placed_by='reseller' THEN 'App'
|
|
END order_placed_by,
|
|
CASE
|
|
WHEN is_campaign='true' THEN 'Campaign Order'
|
|
WHEN is_consignment='true' THEN 'Consignment Order'
|
|
WHEN provider='dsf' THEN 'DSF Order'
|
|
WHEN order_placed_by='merchant' THEN 'Merchant Order'
|
|
WHEN D.name IS NOT NULL THEN 'Channel Order'
|
|
ELSE 'Reseller Order'
|
|
END order_type,
|
|
cast(is_campaign AS varchar) is_campaign
|
|
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
|
|
LEFT JOIN raena_order_management.channel D ON A.channel_id = D.id
|
|
WHERE (payment_status ='Paid' or A.id like 'PL%')
|
|
AND cast(A.created_at+interval'7 hours' AS date) >='$reportDate';
|
|
|
|
drop table if exists raena_analytics.sku_gm_mapping;
|
|
|
|
create table raena_analytics.sku_gm_mapping
|
|
as
|
|
select A.sku ,A.name sku_name,B.name brand_name,C.name as category_name,product_type
|
|
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 ;
|
|
|
|
|
|
DELETE
|
|
FROM raena_analytics.GM_dashboard
|
|
WHERE external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.gm_dimensions_stage1);
|
|
|
|
|
|
INSERT INTO raena_analytics.GM_dashboard
|
|
SELECT A.external_id ,
|
|
B.transaction_date AS transaction_date,
|
|
B.order_discount_amount ,
|
|
B.shipping_cost ,
|
|
A.shipping_province,
|
|
B.coupon_code ,
|
|
A.discount_type ,
|
|
B.coupon_applied_on ,
|
|
brand_name,
|
|
category_name ,
|
|
product_type ,
|
|
product_sku sku,
|
|
sku_name ,
|
|
A.reseller_name ,
|
|
A.reseller_email ,
|
|
A.reseller_mobile ,
|
|
reseller_tier_name tier_name,
|
|
A.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 ,
|
|
coalesce(effective_cogs,coalesce(C.applied_cogs,D.cogs-coalesce(promo,0))) cogs,
|
|
shipping_to order_recipient ,
|
|
CASE
|
|
WHEN brand_name IN ('LUXCRIME',
|
|
'SKINTIFIC',
|
|
'TRUEVE',
|
|
'SANIYE',
|
|
'BEAUDELAB',
|
|
'BRASOV',
|
|
'FACE REPUBLIC',
|
|
'SKIN1004',
|
|
'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',
|
|
'INGRID',
|
|
'ONE THING',
|
|
'ITFER') THEN 'EL/PL'
|
|
END brand_type ,
|
|
Customer_type,
|
|
BB.gm_target,
|
|
B.discount_type discount_promo_type ,
|
|
promo_amount,
|
|
actual_wholesale_price,
|
|
effective_wholesale_price,
|
|
coalesce(case when cogs_type ='REGULAR' then 'Promo' else cogs_type end ,CASE WHEN B.product_sku = D.sku
|
|
AND B.transaction_date::date BETWEEN D.created_at AND last_date
|
|
AND promo IS NOT NULL THEN 'Promo' ELSE 'Non-Promo' END) cogs_flag,
|
|
A.is_campaign,
|
|
CASE
|
|
WHEN A.external_id LIKE '%DSF%' THEN 'Yes'
|
|
ELSE 'No'
|
|
END AS DSF_Flag,
|
|
cogs_discount_type,
|
|
order_type,
|
|
parent_sku,
|
|
is_offender,
|
|
cohort
|
|
FROM raena_analytics.gm_dimensions_stage1 A
|
|
INNER JOIN raena_analytics.base_netsuite_final B ON A.external_id =B.external_id
|
|
LEFT JOIN
|
|
(SELECT B.order_id ,
|
|
A.sku ,
|
|
sum(A.applied_cogs*(case when quantity>0 then quantity else 1 end))/sum(case when quantity>0 then quantity else 1 end ) applied_cogs,
|
|
A.cogs_type
|
|
FROM raena_order_management.fulfillment_detail A
|
|
LEFT JOIN raena_order_management.sales_sub_order_shipment B ON A.shipment_id = B.id
|
|
WHERE applied_cogs IS NOT null and applied_cogs <>0
|
|
group by 1,2,4) C ON B.product_sku = C.sku
|
|
AND A.external_id = C.order_id
|
|
LEFT JOIN
|
|
(SELECT sku,
|
|
cogs,
|
|
promo,
|
|
(created_at+interval'7 hours')::date created_at,
|
|
coalesce(lead((created_at+interval'7 hours')::date-interval'1 day' , 1) over (partition BY sku
|
|
ORDER BY sku,created_at),((CURRENT_DATE+interval'7 hours')+interval'1 day'))::date last_date
|
|
FROM raena_catalog_management.cogs_audit ca
|
|
ORDER BY 1 ,
|
|
4) D ON B.product_sku = D.sku
|
|
AND B.transaction_date::date BETWEEN D.created_at AND last_date
|
|
left join (select distinct order_id ,
|
|
created_at+interval'7 hours' AS created_date,
|
|
parent_sku sku,
|
|
effective_cogs
|
|
from raena_order_management.sales_sub_order
|
|
where price_type is not null and parent_sku not like 'BAZ%')XX
|
|
on A.external_Id=XX.order_id and B.product_sku= XX.sku
|
|
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 B.product_sku = BB.sku
|
|
AND A.reseller_tier_name =BB.tierName
|
|
LEFT JOIN raena_analytics.sku_gm_mapping AAA ON B.product_sku = AAA.sku
|
|
left join (select sku product_table_sku ,is_offender from raena_catalog_management.product ) BBB on B.product_sku=BBB.product_table_sku
|
|
left join (select distinct reseller_id,
|
|
case when max_payment_amount between 0 and 2000000 then '<=2M'
|
|
when max_payment_amount between 2000001 and 10000000 then '2M-10M'
|
|
when max_payment_amount >10000000 then '>10M' end cohort
|
|
from (SELECT reseller_id,
|
|
max(payment_amount) AS max_payment_amount
|
|
FROM
|
|
(SELECT reseller_id AS reseller_id ,
|
|
date_trunc('month',created_at)::date AS created_date,
|
|
sum(payment_amount) payment_amount
|
|
FROM raena_order_management.order o
|
|
where payment_status = 'Paid'
|
|
group by 1,2 ) A
|
|
GROUP BY 1) BB ) CCC on A.reseller_id = CCC.reseller_id
|
|
WHERE A.external_id NOT IN
|
|
(SELECT DISTINCT id
|
|
FROM raena_order_management.order
|
|
WHERE status IN ('Cancelled')
|
|
AND payment_status= 'Paid') ;
|
|
|
|
|
|
|
|
|
|
SELECT max(transaction_date) gm_dashboard_date
|
|
FROM raena_analytics.GM_dashboard;
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.GM_dashboard_v1;
|
|
|
|
|
|
CREATE TABLE raena_analytics.GM_dashboard_v1 AS
|
|
SELECT *
|
|
FROM raena_analytics.GM_dashboard
|
|
WHERE external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.gm_dimensions_stage1);
|
|
|
|
|
|
DELETE
|
|
FROM raena_analytics.GM_dashboard
|
|
WHERE (lower(sku_name) LIKE '%not for sale%'
|
|
OR lower(sku_name) LIKE '%free item%'
|
|
OR lower(sku_name) LIKE '%free gift%'
|
|
OR cogs IS NULL
|
|
OR COGS =0)
|
|
AND external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.gm_dimensions_stage1);
|
|
|
|
|
|
INSERT INTO raena_analytics.GM_dashboard
|
|
SELECT external_id,
|
|
transaction_date,
|
|
order_discount_amount,
|
|
shipping_cost,
|
|
shipping_province,
|
|
coupon_code,
|
|
discount_type,
|
|
coupon_applied_on,
|
|
brand_name,
|
|
category_name,
|
|
product_type,
|
|
sku,
|
|
sku_name,
|
|
reseller_name,
|
|
reseller_email,
|
|
reseller_mobile,
|
|
tier_name,
|
|
reseller_id,
|
|
quantity,
|
|
0 retail_price,
|
|
0 seller_margin,
|
|
0 ,
|
|
0 ,
|
|
0,
|
|
0,
|
|
item_type,
|
|
order_placed_by,
|
|
0 cogs,
|
|
order_recipient,
|
|
brand_type,
|
|
customer_type,
|
|
gm_target,
|
|
discount_promo_type,
|
|
0 promo_amount,
|
|
0,
|
|
0,
|
|
cogs_flag,
|
|
is_campaign,
|
|
dsf_flag,
|
|
cogs_discount_type,
|
|
order_type,
|
|
parent_sku,
|
|
is_offender
|
|
FROM raena_analytics.GM_dashboard_v1
|
|
WHERE (lower(sku_name) LIKE '%not for sale%'
|
|
OR lower(sku_name) LIKE '%free item%'
|
|
OR lower(sku_name) LIKE '%free gift%'
|
|
OR cogs IS NULL
|
|
OR COGS =0)
|
|
AND external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.gm_dimensions_stage1);
|
|
|
|
|
|
|
|
UPDATE raena_analytics.gm_dashboard AA
|
|
SET cogs = 0
|
|
from (SELECT distinct order_id, sku
|
|
from raena_analytics.beau_cogs_update) BB
|
|
where AA.external_id = BB.order_id
|
|
AND AA.sku = BB.sku;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.business_dimensions_stage1;
|
|
|
|
|
|
CREATE TABLE raena_analytics.business_dimensions_stage1 AS
|
|
SELECT distinct A.id AS external_id ,
|
|
reseller_id,
|
|
customer_id,
|
|
cart_id,
|
|
A.flash_sale_id,
|
|
shipping_to,
|
|
payment_status,
|
|
A.status,
|
|
A.stock_type,
|
|
CASE
|
|
WHEN provider='dsf' THEN 'DSF'
|
|
WHEN provider='rdash360' THEN 'RDash'
|
|
WHEN order_placed_by='merchant' THEN 'Merchant'
|
|
WHEN order_placed_by='admin' THEN 'Admin Panel'
|
|
WHEN order_placed_by='reseller' THEN 'App'
|
|
END order_placed_by,
|
|
CASE
|
|
WHEN is_campaign='true' THEN 'Campaign Order'
|
|
WHEN is_consignment='true' THEN 'Consignment Order'
|
|
WHEN provider='dsf' THEN 'DSF Order'
|
|
WHEN order_placed_by='merchant' THEN 'Merchant Order'
|
|
WHEN E.name IS NOT NULL THEN 'Channel Order'
|
|
ELSE 'Reseller Order'
|
|
END order_type,
|
|
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 ,
|
|
replace(cast(A.reseller_info::json->'city' AS varchar),'\"','') reseller_city,
|
|
replace(cast(A.reseller_info::json->'province' AS varchar),'\"','') reseller_province,
|
|
replace(cast(A.reseller_info::json->'name' AS varchar),'\"','') reseller_name,
|
|
replace(cast(A.reseller_info::json->'email' AS varchar),'\"','') reseller_email,
|
|
replace(cast(A.reseller_info::json->'mobile' AS varchar),'\"','') reseller_mobile,
|
|
replace(cast(A.reseller_info::json->'store' AS varchar),'\"','') reseller_store,
|
|
replace(cast(A.reseller_info::json->'tierName' AS varchar),'\"','') reseller_tier_name,
|
|
shipping_address_line1 ,
|
|
shipping_address_line2 ,
|
|
shipping_pincode ,
|
|
shipping_district,
|
|
shipping_city ,
|
|
shipping_province ,
|
|
cast(is_bank_transfer AS varchar) is_bank_transfer ,
|
|
cast(is_campaign AS varchar)is_campaign ,
|
|
discount_type,
|
|
shipping_coupon_discount
|
|
FROM raena_order_management.order A
|
|
LEFT JOIN raena_analytics.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
|
|
LEFT JOIN raena_order_management.channel E ON A.channel_id = E.id
|
|
WHERE cast(A.created_at+interval'7 hours' AS date) >='$reportDate' ;
|
|
|
|
DELETE
|
|
FROM raena_analytics.business_report
|
|
WHERE external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.business_dimensions_stage1);
|
|
|
|
|
|
INSERT INTO raena_analytics.business_report
|
|
SELECT DISTINCT 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,
|
|
A.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 ,
|
|
a.is_campaign ,
|
|
C.Country,
|
|
B.quantity ,
|
|
B.retail_price,
|
|
seller_margin,
|
|
discounted_price,
|
|
loyalty_discount,
|
|
effective_coupon_discount ,
|
|
additional_discount,
|
|
item_type,
|
|
B.discount_type discount_promo_type,
|
|
promo_amount,
|
|
actual_wholesale_price,
|
|
effective_wholesale_price,
|
|
reseller_province,
|
|
CASE
|
|
WHEN A.external_id LIKE '%DSF%' THEN 'Yes'
|
|
ELSE 'No'
|
|
END DSF_flag,
|
|
CASE
|
|
WHEN D.name IN ('LUXCRIME',
|
|
'SKINTIFIC',
|
|
'TRUEVE',
|
|
'SANIYE',
|
|
'BEAUDELAB',
|
|
'BRASOV',
|
|
'FACE REPUBLIC',
|
|
'SKIN1004',
|
|
'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 D.name IN ('W DRESSROOM',
|
|
'BEAUSTA',
|
|
'Dewycel',
|
|
'GLUTANEX',
|
|
'HISTOIRE NATURELLE',
|
|
'FORENCOS',
|
|
'BELLFLOWER',
|
|
'MAXCLINIC',
|
|
'INGRID',
|
|
'ONE THING',
|
|
'ITFER') THEN 'EL/PL'
|
|
END brand_type,
|
|
A.order_type,
|
|
F.is_offender,
|
|
cohort
|
|
FROM raena_analytics.business_dimensions_stage1 A
|
|
LEFT JOIN raena_analytics.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
|
|
left join (select sku product_table_sku ,is_offender from raena_catalog_management.product ) F on B.product_sku= F.product_table_sku
|
|
left join (select distinct reseller_id,
|
|
case when max_payment_amount between 0 and 2000000 then '<=2M'
|
|
when max_payment_amount between 2000001 and 10000000 then '2M-10M'
|
|
when max_payment_amount >10000000 then '>10M' end cohort
|
|
from (SELECT reseller_id,
|
|
max(payment_amount) AS max_payment_amount
|
|
FROM
|
|
(SELECT reseller_id AS reseller_id ,
|
|
date_trunc('month',created_at)::date AS created_date,
|
|
sum(payment_amount) payment_amount
|
|
FROM raena_order_management.order o
|
|
where payment_status = 'Paid' or id like 'PL%'
|
|
group by 1,2 ) A
|
|
GROUP BY 1) BB ) CCC on A.reseller_id = CCC.reseller_id
|
|
WHERE A.external_id NOT IN
|
|
(SELECT DISTINCT id
|
|
FROM raena_order_management.order
|
|
WHERE status IN ('Cancelled')
|
|
AND payment_status= 'Paid' );
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.business_report_v1;
|
|
|
|
|
|
CREATE TABLE raena_analytics.business_report_v1 AS
|
|
SELECT *
|
|
FROM raena_analytics.business_report
|
|
WHERE external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.business_dimensions_stage1);
|
|
|
|
|
|
DELETE
|
|
FROM raena_analytics.business_report USING
|
|
(SELECT DISTINCT sku,
|
|
sku_name,
|
|
external_id
|
|
FROM raena_analytics.gm_dashboard gd
|
|
WHERE lower(sku_name) LIKE '%not for sale%'
|
|
OR lower(sku_name) LIKE '%free item%'
|
|
OR lower(sku_name) LIKE '%free gift%'
|
|
OR cogs IS NULL
|
|
OR COGS =0) B
|
|
WHERE raena_analytics.business_report.external_id = B.external_id
|
|
AND raena_analytics.business_report.sku=B.sku
|
|
AND raena_analytics.business_report.external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.business_dimensions_stage1);
|
|
|
|
|
|
INSERT INTO raena_analytics.business_report
|
|
SELECT external_id,
|
|
created_date,
|
|
order_discount_amount,
|
|
shipping_cost,
|
|
coupon_code,
|
|
brand_name,
|
|
product_type,
|
|
category_name,
|
|
sku_name,
|
|
sku,
|
|
reseller_id,
|
|
customer_id,
|
|
cart_id,
|
|
flash_sale_id,
|
|
shipping_to,
|
|
payment_status,
|
|
status,
|
|
stock_type,
|
|
order_placed_by,
|
|
order_warehouse,
|
|
channel_id,
|
|
medium,
|
|
marketplace,
|
|
provider,
|
|
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,
|
|
country,
|
|
quantity,
|
|
0 retail_price,
|
|
0 seller_margin,
|
|
0 discount_price,
|
|
0 loyalty_discount,
|
|
0 effective_coupon_discount,
|
|
0 additional_discount,
|
|
item_type,
|
|
discount_promo_type,
|
|
0 promo_amount,
|
|
0 actual_wholesale_price,
|
|
0 effective_wholesale_price,
|
|
reseller_province,
|
|
dsf_flag,
|
|
brand_type,
|
|
order_type,
|
|
is_offender,
|
|
cohort
|
|
FROM
|
|
(SELECT raena_analytics.business_report_v1.*
|
|
FROM raena_analytics.business_report_v1
|
|
INNER JOIN
|
|
(SELECT DISTINCT sku,
|
|
sku_name,
|
|
external_id
|
|
FROM raena_analytics.gm_dashboard gd
|
|
WHERE lower(sku_name) LIKE '%not for sale%'
|
|
OR lower(sku_name) LIKE '%free item%'
|
|
OR lower(sku_name) LIKE '%free gift%'
|
|
OR cogs IS NULL
|
|
OR COGS =0) B ON raena_analytics.business_report_v1.external_id = B.external_id
|
|
AND raena_analytics.business_report_v1.sku=B.sku) A
|
|
WHERE external_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.business_dimensions_stage1);
|
|
|
|
SELECT max(created_date) business_date
|
|
FROM raena_analytics.business_report;
|
|
|
|
|
|
DELETE
|
|
FROM raena_analytics.base_netsuite_final
|
|
WHERE external_id IN
|
|
(SELECT DISTINCT order_id
|
|
FROM raena_analytics.sk_product) or external_id is null;
|
|
|
|
DELETE
|
|
FROM raena_analytics.business_report
|
|
WHERE external_Id IN
|
|
(SELECT DISTINCT order_id
|
|
FROM raena_analytics.sk_product) or external_id is null;
|
|
|
|
DELETE
|
|
FROM raena_analytics.OM_GM_DB_Product_category
|
|
WHERE external_Id IN
|
|
(SELECT DISTINCT order_id
|
|
FROM raena_analytics.sk_product) or external_id is null;
|
|
|
|
|
|
DELETE
|
|
FROM raena_analytics.gm_dashboard
|
|
WHERE external_Id IN
|
|
(SELECT DISTINCT order_id
|
|
FROM raena_analytics.sk_product) or external_id is null;
|
|
|
|
|
|
|
|
insert into raena_analytics.base_netsuite_final
|
|
select
|
|
order_id ,
|
|
transaction_date,
|
|
sum(pre_discount_rev-post_discount_revenue) over(partition by order_id) order_discount_amount,
|
|
sum(shipping_fee_sku_level) over (partition by order_id)shipping_cost,
|
|
'SK Product'coupon_code,
|
|
''coupon_applied_on,
|
|
''tier,
|
|
sku product_sku,
|
|
quantity,
|
|
pre_discount_rev/quantity retail_price,
|
|
0 seller_margin,
|
|
post_discount_revenue/quantity discounted_price,
|
|
(pre_discount_rev-post_discount_revenue)/quantity coupon_discount,
|
|
(pre_discount_rev-post_discount_revenue)/quantity effective_coupon_discount,
|
|
0 loyalty_discount,
|
|
0 additional_discount,
|
|
'' item_type,
|
|
0 diff,
|
|
'' discount_type,
|
|
0 promo_amount,
|
|
pre_discount_rev/quantity actual_wholesale_price,
|
|
pre_discount_rev/quantity effective_wholesale_price,
|
|
false is_campaign,
|
|
'' sales_sub_order_id,
|
|
'' cogs_discount_type,
|
|
'' parent_sku
|
|
from raena_analytics.sk_product;
|
|
|
|
|
|
update raena_analytics.gm_dashboard
|
|
set retail_price = 79900.00
|
|
where sku ='FTG001' and transaction_date::date between '2023-06-01' and '2023-07-08';
|
|
|
|
update raena_analytics.gm_dashboard
|
|
set additional_discount = ((retail_price-seller_margin)-discounted_price)
|
|
where sku ='FTG001' and transaction_date::date between '2023-06-01' and '2023-07-08'
|
|
and additional_discount >0;
|
|
|
|
|
|
|
|
update raena_analytics.gm_dashboard
|
|
set effective_coupon_discount = ((retail_price-seller_margin)-discounted_price)
|
|
where sku ='FTG001' and transaction_date::date between '2023-06-01' and '2023-07-08'
|
|
and effective_coupon_discount >0;
|
|
|
|
|
|
update raena_analytics.OM_GM_DB_Product_category
|
|
set retail_price = 79900.00
|
|
where sku ='FTG001' and transaction_date::date between '2023-06-01' and '2023-07-08';
|
|
|
|
update raena_analytics.OM_GM_DB_Product_category
|
|
set additional_discount = ((retail_price-seller_margin)-discounted_price)
|
|
where sku ='FTG001' and transaction_date::date between '2023-06-01' and '2023-07-08'
|
|
and additional_discount >0;
|
|
|
|
|
|
|
|
insert into raena_analytics.gm_dashboard
|
|
select
|
|
order_id ,
|
|
transaction_date,
|
|
sum(pre_discount_rev-post_discount_revenue) over(partition by order_id) order_discount_amount,
|
|
sum(shipping_fee_sku_level) over (partition by order_id)shipping_cost,
|
|
B.province shipping_province,
|
|
'SK Product'coupon_code,
|
|
'' discount_type,
|
|
'' coupon_applied_on,
|
|
brand_name,
|
|
category_name,
|
|
product_type,
|
|
A.sku,
|
|
sku_name,
|
|
B.name reseller_name,
|
|
reseller reseller_email,
|
|
B.mobile reseller_mobile,
|
|
D.name tier_name,
|
|
B.id reseller_id,
|
|
quantity,
|
|
pre_discount_rev/quantity retail_price,
|
|
0 seller_margin,
|
|
post_discount_revenue/quantity discounted_price,
|
|
0 additional_discount,
|
|
0 loyalty_discount,
|
|
(pre_discount_rev-post_discount_revenue)/quantity effective_coupon_discount,
|
|
'' item_type,
|
|
'Admin Panel'order_placed_by,
|
|
cogs/quantity cogs,
|
|
'' order_recipient,
|
|
'' brand_type,
|
|
'SK product' customer_type,
|
|
gm_target,
|
|
'' discount_promo_type,
|
|
0 promo_amount,
|
|
(pre_discount_rev)/quantity actual_wholesale_price,
|
|
(pre_discount_rev)/quantity effective_wholesale_price,
|
|
'' cogs_flag,
|
|
false is_campaign,
|
|
false dsf_flag,
|
|
'' cogs_discount_type,
|
|
'SK product' order_type,
|
|
'' parent_sku,
|
|
'' is_offender,
|
|
'' cohort
|
|
from raena_analytics.sk_product A inner join raena_user_management.user B on reseller = email
|
|
inner join (select sku ,p.name sku_name,B.name brand_name , C.name category_name , product_type from raena_catalog_management.product p
|
|
left join raena_catalog_management.brand b on p.brand_id= B.id
|
|
left join raena_catalog_management.category c on p.category_id=C.id) C
|
|
on A.sku = C.sku
|
|
inner join raena_user_management.tier D on B.tier_id = D.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 D.name =BB.tierName ;
|
|
|
|
|
|
insert into raena_analytics.business_report
|
|
select
|
|
order_id ,
|
|
transaction_date,
|
|
sum(pre_discount_rev-post_discount_revenue) over(partition by order_id) order_discount_amount,
|
|
sum(shipping_fee_sku_level) over (partition by order_id)shipping_cost,
|
|
'SK Product'coupon_code,
|
|
brand_name,
|
|
product_type,
|
|
category_name,
|
|
sku_name,
|
|
A.sku,
|
|
B.id reseller_id,
|
|
null customer_id,
|
|
null cart_id,
|
|
null flash_sale_id,
|
|
null shipping_to,
|
|
'Paid' payment_status,
|
|
'' status,
|
|
'' stock_type,
|
|
'Admin Panel' order_placed_by,
|
|
'' order_warehouse,
|
|
'' channel_id,
|
|
'' medium,
|
|
'' marketplace,
|
|
'' provider,
|
|
D.name tier,
|
|
city reseller_city,
|
|
B.name reseller_name,
|
|
reseller reseller_email,
|
|
mobile reseller_mobile,
|
|
store reseller_store,
|
|
address_line1 shipping_address_line1,
|
|
address_line2 shipping_address_line2,
|
|
pincode shipping_pincode,
|
|
district shipping_district,
|
|
city shipping_city,
|
|
province shipping_province,
|
|
false is_bank_transfer,
|
|
false is_campaign,
|
|
'' country,
|
|
quantity,
|
|
pre_discount_rev/quantity retail_price,
|
|
0 seller_margin,
|
|
post_discount_revenue/quantity discounted_price,
|
|
0 loyalty_discount,
|
|
(pre_discount_rev-post_discount_revenue)/quantity effective_coupon_discount,
|
|
0 additional_discount,
|
|
'' item_type,
|
|
'' discount_promo_type,
|
|
0 promo_amount,
|
|
(pre_discount_rev)/quantity actual_wholesale_price,
|
|
(pre_discount_rev)/quantity effective_wholesale_price,
|
|
province reseller_province,
|
|
false dsf_flag,
|
|
case when \"el/pl_flag\"= 'Yes' then 'EL/PL' end brand_type,
|
|
'SK Product 'order_type,
|
|
'' cohort
|
|
from raena_analytics.sk_product A inner join raena_user_management.user B on reseller = email
|
|
inner join (select sku ,p.name sku_name,B.name brand_name , C.name category_name , product_type from raena_catalog_management.product p
|
|
left join raena_catalog_management.brand b on p.brand_id= B.id
|
|
left join raena_catalog_management.category c on p.category_id=C.id) C
|
|
on A.sku = C.sku
|
|
inner join raena_user_management.tier D on B.tier_id = D.id ;
|
|
|
|
|
|
|
|
|
|
|
|
DELETE
|
|
FROM raena_analytics.OM_GM_DB_Product_category
|
|
WHERE external_Id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.gm_dimensions_stage1);
|
|
|
|
|
|
INSERT INTO raena_analytics.OM_GM_DB_Product_category
|
|
SELECT external_id, transaction_date,
|
|
\"Time\",
|
|
order_discount_amount,
|
|
shipping_cost,
|
|
coupon_code ,
|
|
discount_type ,
|
|
coupon_applied_on ,
|
|
brand_name,
|
|
category_name ,
|
|
product_type ,
|
|
sku,
|
|
sku_name ,
|
|
reseller_name ,
|
|
reseller_email ,
|
|
base.reseller_mobile ,
|
|
tier_name ,
|
|
reseller_id ,
|
|
quantity ,
|
|
retail_price ,
|
|
seller_margin ,
|
|
discounted_price ,
|
|
additional_discount,
|
|
item_type ,
|
|
order_placed_by ,
|
|
cogs,
|
|
shipping_province ,
|
|
Flag ,
|
|
wholesale_price,
|
|
order_recipient,
|
|
brand_type,
|
|
Customer_type,
|
|
gm_target,
|
|
discount_promo_type,
|
|
promo_amount,
|
|
actual_wholesale_price,
|
|
effective_wholesale_price,
|
|
CASE WHEN date_trunc('Month',base.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
|
AND date_trunc('Month',base.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
|
WHEN date_trunc('Month',base.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
|
AND date_trunc('Month',base.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
|
WHEN date_trunc('Month',base.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
|
AND date_trunc('Month',base.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
|
WHEN (date_part('Month',first_install_date) =2 or date_part('Month',First_transaction_date)=2 ) and
|
|
date_trunc('Month',base.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 28
|
|
AND date_trunc('Month',base.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
|
WHEN (date_part('Month',first_install_date) =2 or date_part('Month',First_transaction_date)=2 ) and
|
|
date_trunc('Month',base.transaction_date)::date-date_trunc('Month',first_install_date)::date>=28
|
|
AND date_trunc('Month',base.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=28 THEN 'Existing'
|
|
ELSE 'Na'
|
|
END AS New_existing_flag,
|
|
is_campaign,
|
|
dsf_flag,
|
|
cogs_flag,
|
|
cogs_discount_type,
|
|
order_type,
|
|
parent_sku,
|
|
First_transaction_date,
|
|
is_offender,
|
|
cohort
|
|
FROM
|
|
(SELECT A.external_id ,
|
|
cast(transaction_date AS date) 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,
|
|
discount_promo_type,
|
|
promo_amount,
|
|
actual_wholesale_price,
|
|
effective_wholesale_price,
|
|
is_campaign,
|
|
A.dsf_flag,
|
|
A.cogs_flag,
|
|
A.cogs_discount_type,
|
|
A.order_type,
|
|
parent_sku,
|
|
is_offender,
|
|
cohort
|
|
FROM raena_analytics.GM_dashboard A
|
|
LEFT JOIN
|
|
(SELECT DISTINCT external_id
|
|
FROM
|
|
(SELECT external_id,
|
|
discounted_price ,
|
|
sku ,
|
|
(((retail_price-seller_margin)*quantity) -(cogs*quantity))/(CASE WHEN retail_price<>0 THEN (retail_price-seller_margin)*quantity END) Pre_discount,
|
|
CASE WHEN discounted_price>0 THEN ((discounted_price*quantity) -(cogs*quantity))/(discounted_price*quantity) ELSE 0 END Post_discount
|
|
FROM raena_analytics.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 raena_analytics.pricing_sheet P ON A.sku=p.skucode
|
|
WHERE cast(transaction_date AS date) >='$reportDate'
|
|
AND A.external_id NOT IN
|
|
(SELECT DISTINCT id
|
|
FROM raena_order_management.order
|
|
WHERE status IN ('Cancelled')
|
|
AND payment_status= 'Paid')) base
|
|
LEFT JOIN raena_analytics.user_type_table flag ON replace(base.reseller_mobile,'+','')=flag.reseller_mobile;
|
|
|
|
SELECT max(transaction_date) om_gm_db_product_date
|
|
FROM raena_analytics.OM_GM_DB_Product_category;
|
|
|
|
update raena_analytics.gm_dashboard
|
|
set cogs_discount_type='BUY_N_GET_X'
|
|
where external_id in (select distinct external_id from raena_analytics.gm_dashboard where sku like '______X');
|
|
|
|
update raena_analytics.om_gm_db_product_category
|
|
set cogs_discount_type='BUY_N_GET_X'
|
|
where external_id in (select distinct external_id from raena_analytics.om_gm_db_product_category where sku like '______X');
|
|
|
|
|
|
|
|
update raena_analytics.OM_GM_DB_Product_category
|
|
set retail_price=0,
|
|
seller_margin=0,
|
|
additional_discount=0,
|
|
wholesale_price=0,
|
|
promo_amount=0,
|
|
actual_wholesale_price=0,
|
|
effective_wholesale_price=0,
|
|
cogs =0
|
|
where sku = 'BEL006' and date_trunc('Month',transaction_date)::date between '2023-04-01' and '2023-05-01' and discounted_price in (0,1,2,3,4,5);
|
|
|
|
update raena_analytics.gm_dashboard
|
|
set retail_price=0,
|
|
seller_margin=0,
|
|
loyalty_discount=0,
|
|
effective_coupon_discount=0,
|
|
additional_discount=0,
|
|
promo_amount=0,
|
|
actual_wholesale_price=0,
|
|
effective_wholesale_price=0,
|
|
cogs =0
|
|
where sku = 'BEL006' and date_trunc('Month',transaction_date)::date between '2023-04-01' and '2023-05-01' and discounted_price in (0,1,2,3,4,5);
|
|
|
|
DELETE
|
|
FROM raena_analytics.GM_GROWTH_TAB1
|
|
WHERE created_date >='$reportDate';
|
|
|
|
INSERT INTO raena_analytics.GM_GROWTH_TAB1
|
|
SELECT tier,
|
|
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,
|
|
base.reseller_mobile,
|
|
Number_of_Orders ,
|
|
RSP ,
|
|
wholesale_price ,
|
|
Payment_Price ,
|
|
Effective_Coupon_Discount,
|
|
Total_Loyalty_point,
|
|
additional_discount,
|
|
seller_margin,
|
|
AOV,
|
|
quantity,
|
|
discount_promo_type,
|
|
promo_amount,
|
|
actual_wholesale_price,
|
|
effective_wholesale_price,
|
|
reseller_province,
|
|
is_campaign,
|
|
dsf_flag,
|
|
brand_type,
|
|
CASE
|
|
WHEN date_trunc('Month',base.created_date)::date = date_trunc('Month',first_install_date)::date
|
|
AND date_trunc('Month',base.created_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
|
WHEN date_trunc('Month',base.created_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
|
AND date_trunc('Month',base.created_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
|
WHEN date_trunc('Month',base.created_date)::date-date_trunc('Month',first_install_date)::date>=30
|
|
AND date_trunc('Month',base.created_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
|
ELSE 'Na'
|
|
END AS New_existing_flag,
|
|
external_id,
|
|
order_type,
|
|
First_transaction_date,
|
|
is_offender,
|
|
cohort
|
|
FROM
|
|
(SELECT tier,
|
|
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,
|
|
reseller_mobile,
|
|
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,
|
|
discount_promo_type,
|
|
sum(promo_amount) promo_amount,
|
|
sum(actual_wholesale_price* quantity) actual_wholesale_price,
|
|
sum(effective_wholesale_price*quantity) effective_wholesale_price,
|
|
reseller_province,
|
|
is_campaign,
|
|
dsf_flag,
|
|
brand_type,
|
|
external_id,
|
|
order_type,
|
|
is_offender,
|
|
cohort
|
|
FROM
|
|
(SELECT DISTINCT coalesce(a.tier,'NA') AS tier,
|
|
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,
|
|
reseller_mobile,
|
|
external_id,
|
|
retail_price,
|
|
quantity,
|
|
seller_margin,
|
|
discounted_price,
|
|
effective_coupon_discount,
|
|
loyalty_discount,
|
|
additional_discount,
|
|
discount_promo_type,
|
|
promo_amount,
|
|
actual_wholesale_price,
|
|
effective_wholesale_price,
|
|
reseller_province,
|
|
is_campaign,
|
|
dsf_flag,
|
|
brand_type,
|
|
order_type,
|
|
is_offender,
|
|
cohort,
|
|
row_number() over (partition BY external_id
|
|
ORDER BY external_id) AS R
|
|
FROM raena_analytics.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) >= '$reportDate'
|
|
AND channel_id IS NOT NULL) b ON a.external_id=b.id
|
|
WHERE (payment_status='Paid' or a.external_id like 'PL%')
|
|
AND created_date >= '$reportDate') A
|
|
GROUP BY tier,
|
|
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,
|
|
reseller_mobile,
|
|
external_id,
|
|
discount_promo_type,
|
|
reseller_province,
|
|
is_campaign,
|
|
dsf_flag,
|
|
brand_type,
|
|
order_type,
|
|
is_offender,
|
|
cohort) base
|
|
LEFT JOIN raena_analytics.user_type_table flag ON replace(base.reseller_mobile,'+','')=flag.reseller_mobile;
|
|
|
|
SELECT max(created_date) gm_growth_date
|
|
FROM raena_analytics.GM_GROWTH_TAB1;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.GM_GROWTH_TAB2;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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 raena_analytics.GM_GROWTH_TAB1
|
|
UNION
|
|
SELECT 'quarter' AS frequency,
|
|
To_char(created_date,'quarter') AS month_name,
|
|
date_part('quarter',created_date) AS sort ,
|
|
*
|
|
FROM raena_analytics.GM_GROWTH_TAB1
|
|
UNION
|
|
SELECT 'month' AS frequency,
|
|
To_char(created_date,'month') AS month_name,
|
|
date_part('month',created_date) AS sort ,
|
|
*
|
|
FROM raena_analytics.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 raena_analytics.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 raena_analytics.GM_GROWTH_TAB1
|
|
ORDER BY 3;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.GM_GROWTH_TAB3;
|
|
CREATE TABLE raena_analytics.GM_GROWTH_TAB3 AS
|
|
(
|
|
SELECT 'year' AS frequency,
|
|
'All' AS brand_name,
|
|
'All' AS product_type,
|
|
'All' AS category_name,
|
|
'All' AS sku,
|
|
'All' AS sku_name,tier_name,new_existing_flag,
|
|
cast(date_part('year',transaction_date) AS varchar) AS time,
|
|
cast(date_part('year',transaction_date) AS int) AS sort,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 7,8,9,10,11,12,13,14,15,16,17
|
|
union
|
|
SELECT 'year' AS frequency,
|
|
brand_name,
|
|
product_type,
|
|
category_name,
|
|
sku,
|
|
sku_name,tier_name,new_existing_flag,
|
|
cast(date_part('year',transaction_date) AS varchar) AS time,
|
|
cast(date_part('year',transaction_date) AS int) AS sort,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
|
|
)
|
|
union
|
|
(
|
|
SELECT 'quarter' AS frequency,
|
|
'All' AS brand_name,
|
|
'All' AS product_type,
|
|
'All' AS category_name,
|
|
'All' AS sku,
|
|
'All' AS sku_name,tier_name,new_existing_flag,
|
|
To_char(transaction_date,'quarter') AS month_name,
|
|
date_part('quarter',transaction_date) as sort ,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 7,8,9,10,11,12,13,14,15,16,17
|
|
union
|
|
SELECT 'quarter' AS frequency,
|
|
brand_name,
|
|
product_type,
|
|
category_name,
|
|
sku,
|
|
sku_name,tier_name,new_existing_flag,
|
|
To_char(transaction_date,'quarter') AS month_name,
|
|
date_part('quarter',transaction_date) as sort ,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
|
|
)
|
|
union
|
|
(
|
|
SELECT 'month' AS frequency,
|
|
'All' AS brand_name,
|
|
'All' AS product_type,
|
|
'All' AS category_name,
|
|
'All' AS sku,
|
|
'All' AS sku_name,tier_name,new_existing_flag,
|
|
To_char(transaction_date,'month') AS month_name,
|
|
date_part('month',transaction_date) as sort ,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 7,8,9,10,11,12,13,14,15,16,17
|
|
union
|
|
SELECT 'month' AS frequency,
|
|
brand_name,
|
|
product_type,
|
|
category_name,
|
|
sku,
|
|
sku_name,tier_name,new_existing_flag,
|
|
To_char(transaction_date,'month') AS month_name,
|
|
date_part('month',transaction_date) as sort ,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17)
|
|
union
|
|
(
|
|
SELECT 'week' AS frequency,
|
|
'All' AS brand_name,
|
|
'All' AS product_type,
|
|
'All' AS category_name,
|
|
'All' AS sku,
|
|
'All' AS sku_name,tier_name,new_existing_flag,
|
|
To_char(transaction_date,'week') AS month_name,
|
|
cast(left(To_char(transaction_date,'week'),1) as int) AS sort ,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 7,8,9,10,11,12,13,14,15,16,17
|
|
union
|
|
SELECT 'week' AS frequency,
|
|
brand_name,
|
|
product_type,
|
|
category_name,
|
|
sku,
|
|
sku_name,tier_name,new_existing_flag,
|
|
To_char(transaction_date,'week') AS month_name,
|
|
cast(left(To_char(transaction_date,'week'),1) as int) AS sort ,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
|
|
)
|
|
union
|
|
(
|
|
SELECT 'day' AS frequency,
|
|
'All' AS brand_name,
|
|
'All' AS product_type,
|
|
'All' AS category_name,
|
|
'All' AS sku,
|
|
'All' AS sku_name,tier_name,new_existing_flag,
|
|
cast(date_part('day',transaction_date) as varchar) AS month_name,
|
|
cast(date_part('day',transaction_date) AS int) AS sort ,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 7,8,9,10,11,12,13,14,15,16,17
|
|
union
|
|
SELECT 'day' AS frequency,
|
|
brand_name,
|
|
product_type,
|
|
category_name,
|
|
sku,
|
|
sku_name,tier_name,new_existing_flag,
|
|
cast(date_part('day',transaction_date) as varchar) AS month_name,
|
|
cast(date_part('day',transaction_date) AS int) AS sort ,o.status,
|
|
order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
|
|
order_type,
|
|
sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
|
|
from raena_analytics.OM_GM_DB_Product_category a
|
|
inner join (select distinct id,status from raena_order_management.order) o on a.external_id=o.id
|
|
group by 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
|
|
)
|
|
ORDER BY 3;
|
|
|
|
DROP TABLE raena_analytics.shipping_fee_base_table;
|
|
|
|
|
|
CREATE TABLE raena_analytics.shipping_fee_base_table AS
|
|
SELECT A.order_id ,
|
|
sum(CASE WHEN A.sales_sub_order_shipment_id = C.reference_id THEN ((C.shipping_amount)/(C.order_weight))*(C.final_weight) ELSE B.shipping_amount END) shipping_amount
|
|
FROM
|
|
(SELECT DISTINCT order_id ,
|
|
sales_sub_order_shipment_id
|
|
FROM raena_order_management.sales_sub_order) A
|
|
LEFT JOIN raena_order_management.sales_sub_order_shipment B ON A.sales_sub_order_shipment_id = B.id
|
|
LEFT JOIN raena_analytics.OM_Logistic_final_shipping C ON A.sales_sub_order_shipment_id = C.reference_id
|
|
GROUP BY A.order_id;
|
|
|
|
|
|
DROP TABLE raena_analytics.shipping_fee_sku_base_stage1 ;
|
|
|
|
|
|
CREATE TABLE raena_analytics.shipping_fee_sku_base_stage1 AS
|
|
SELECT A.order_id ,
|
|
A.id ,
|
|
coalesce(B.sku,A.parent_sku) Sku ,
|
|
A.sales_sub_order_shipment_id ,
|
|
coalesce((B.quantity*A.quantity),A.quantity) Quantity,
|
|
CASE
|
|
WHEN A.sales_sub_order_shipment_id=D.reference_id THEN ((D.shipping_amount)/(D.order_weight))*(D.final_weight)
|
|
ELSE C.shipping_amount
|
|
END shipping_amount ,
|
|
A.product_class
|
|
FROM raena_order_management.sales_sub_order A
|
|
LEFT JOIN raena_order_management.sales_sub_order_parent_child B ON A.id = B.sales_sub_order_id
|
|
LEFT JOIN raena_order_management.sales_sub_order_shipment C ON A.sales_sub_order_shipment_id = C.id
|
|
LEFT JOIN raena_analytics.OM_Logistic_final_shipping D ON A.sales_sub_order_shipment_id=D.reference_id ;
|
|
|
|
|
|
DROP TABLE raena_analytics.weight_sku_table;
|
|
|
|
|
|
CREATE TABLE raena_analytics.weight_sku_table AS
|
|
SELECT sku,
|
|
CASE
|
|
WHEN weight>=volume_weight THEN weight
|
|
ELSE volume_weight
|
|
END weight
|
|
FROM
|
|
(SELECT a.sku,
|
|
weight,
|
|
(height*width*LENGTH)/6000 AS volume_weight
|
|
FROM raena_catalog_management.product a
|
|
WHERE CLASS<>'Bundle') A;
|
|
|
|
|
|
DROP TABLE raena_analytics.shipping_fee_sku_base_stage2_v1;
|
|
|
|
|
|
CREATE TABLE raena_analytics.shipping_fee_sku_base_stage2_v1 AS
|
|
SELECT order_id ,
|
|
sales_sub_order_shipment_id,
|
|
CASE
|
|
WHEN (sum(B.weight*Quantity))<1.3 THEN 1
|
|
WHEN (sum(B.weight*Quantity))>=1.3
|
|
AND (ABS(sum(B.weight*Quantity)) - FLOOR(ABS(sum(B.weight*Quantity)))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(sum(B.weight*Quantity))+1
|
|
ELSE FLOOR(sum(B.weight*Quantity))
|
|
END AS sku_weight
|
|
FROM raena_analytics.shipping_fee_sku_base_stage1 A
|
|
LEFT JOIN raena_analytics.weight_sku_table B ON A.sku = B.sku
|
|
GROUP BY 1,
|
|
2;
|
|
|
|
|
|
DROP TABLE raena_analytics.shipping_fee_sku_base_stage2;
|
|
|
|
|
|
CREATE TABLE raena_analytics.shipping_fee_sku_base_stage2 AS
|
|
SELECT A.*,
|
|
B.weight*sku_weight/sum(B.weight) over(partition BY A.order_id , A.sales_sub_order_shipment_id) Final_weight
|
|
FROM raena_analytics.shipping_fee_sku_base_stage1 A
|
|
LEFT JOIN raena_analytics.shipping_fee_sku_base_stage2_v1 C ON A.order_id = C.order_id
|
|
AND A.sales_sub_order_shipment_id = C.sales_sub_order_shipment_id
|
|
LEFT JOIN raena_analytics.weight_sku_table B ON A.sku = B.sku ;
|
|
|
|
|
|
DROP TABLE raena_analytics.shipping_fee_sku_base_stage3;
|
|
|
|
CREATE TABLE raena_analytics.shipping_fee_sku_base_stage3 AS
|
|
SELECT A.*,
|
|
(Final_weight*coalesce(coalesce(B.Calculated_shipping_fee,C.Calculated_shipping_fee),shipping_amount))/sum(Final_weight) over(partition BY A.sales_sub_order_shipment_id) new_shipment_amount
|
|
FROM raena_analytics.shipping_fee_sku_base_stage2 A
|
|
left join raena_analytics.calculated_ninja_jne_oct_shipping_fee B on A.order_id = B.order_id and A.sales_sub_order_shipment_id = B.reference_id
|
|
left join raena_analytics.calculated_feb_shipping_fee C on A.order_id = C.order_id and A.sales_sub_order_shipment_id = C.reference_id ;
|
|
|
|
DROP TABLE raena_analytics.sku_level_shipping_fee_final;
|
|
|
|
|
|
CREATE TABLE raena_analytics.sku_level_shipping_fee_final AS
|
|
SELECT order_id ,
|
|
sku,
|
|
product_class ,
|
|
sum(new_shipment_amount) new_shipment_amount
|
|
FROM raena_analytics.shipping_fee_sku_base_stage3
|
|
GROUP BY 1,
|
|
2,
|
|
3;
|
|
|
|
|
|
DROP TABLE raena_analytics.shipping_fee_sku_base_old_stage1;
|
|
|
|
|
|
CREATE TABLE raena_analytics.shipping_fee_sku_base_old_stage1 AS
|
|
SELECT external_id ,
|
|
A.sku ,
|
|
quantity,
|
|
shipping_cost ,
|
|
CASE
|
|
WHEN item_type LIKE '%Bundle%' THEN 'Bundle'
|
|
ELSE 'Product'
|
|
END product_class,
|
|
weight*Quantity sku_weight,
|
|
CASE
|
|
WHEN (weight*Quantity)<1.3 THEN 1
|
|
WHEN (weight*Quantity)>=1.3
|
|
AND (ABS((weight*Quantity)) - FLOOR(ABS((weight*Quantity)))) BETWEEN 0.3 AND 0.999999 THEN FLOOR((weight*Quantity))+1
|
|
ELSE FLOOR((weight*Quantity))
|
|
END AS final_weight
|
|
FROM raena_analytics.gm_dashboard A
|
|
LEFT JOIN raena_analytics.weight_sku_table B ON A.sku = B.sku
|
|
where A.order_type <> 'SK product';
|
|
|
|
|
|
DROP TABLE raena_analytics.shipping_fee_sku_base_old_stage2;
|
|
|
|
|
|
CREATE TABLE raena_analytics.shipping_fee_sku_base_old_stage2 AS
|
|
SELECT *,
|
|
(sku_weight*shipping_cost)/sum(sku_weight) over(partition BY external_id) new_shipment_amount
|
|
FROM raena_analytics.shipping_fee_sku_base_old_stage1 ;
|
|
|
|
|
|
DROP TABLE raena_analytics.sku_level_shipping_fee_old_final;
|
|
|
|
|
|
CREATE TABLE raena_analytics.sku_level_shipping_fee_old_final AS
|
|
SELECT external_id ,
|
|
sku,
|
|
product_class ,
|
|
sum(new_shipment_amount) new_shipment_amount
|
|
FROM raena_analytics.shipping_fee_sku_base_old_stage2
|
|
GROUP BY 1,
|
|
2,
|
|
3
|
|
union
|
|
select distinct order_id , sku , 'Product' ,
|
|
sum(shipping_fee_sku_level)
|
|
from raena_analytics.sk_product
|
|
group by 1,2 ;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.payment_order_stage;
|
|
|
|
|
|
CREATE TABLE raena_analytics.payment_order_stage AS
|
|
SELECT id,
|
|
max(created_at) created_at,
|
|
replace(cast(jsonb_array_elements(order_ids) AS varchar),'\"','') AS order_id,
|
|
payment_provider,
|
|
payment_type,
|
|
payment_category,
|
|
payment_channel
|
|
FROM raena_order_management.payment
|
|
where payment_status ='Paid'
|
|
GROUP BY 1,
|
|
3,
|
|
4,
|
|
5,
|
|
6,
|
|
7;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.payment_order;
|
|
|
|
|
|
CREATE TABLE raena_analytics.payment_order AS
|
|
SELECT A.id ,
|
|
A.created_at,
|
|
A.order_id,
|
|
payment_provider,
|
|
payment_type,
|
|
payment_category,
|
|
payment_channel
|
|
FROM raena_analytics.payment_order_stage A
|
|
INNER JOIN
|
|
(SELECT order_id ,
|
|
max(created_at) max_created_at
|
|
FROM raena_analytics.payment_order_stage
|
|
GROUP BY order_id) B ON A.created_at= B.max_created_at
|
|
AND A.order_id = B.order_id;
|
|
|
|
select count(1) from raena_analytics.payment_order;
|
|
|
|
drop table raena_analytics.om_app_admin_dump_table;
|
|
|
|
|
|
CREATE TABLE raena_analytics.om_app_admin_dump_table AS
|
|
/*SELECT raena_order_management.order.id AS order_id,
|
|
(raena_order_management.order.created_at+interval '7 hours') AS order_date,
|
|
(raena_order_management.order.created_at+interval '7 hours') AS id_order_date,
|
|
status AS order_status,
|
|
Payment_status,
|
|
D.name AS channel_name,
|
|
cast(is_bank_transfer AS varchar) AS bank_transfer,
|
|
cast(is_campaign AS varchar) AS campaign,
|
|
dynamic_price,
|
|
total_dynamic_and_tier_price AS total_tier_price,
|
|
total_retail_price AS total_MRP,
|
|
tier_discount AS total_tier_discount,
|
|
discount_amount AS coupon_discount,
|
|
payment_amount,
|
|
shipping_amount AS Actual_shipping_amount,
|
|
applied_shipping_amount,
|
|
shipping_coupon_discount,
|
|
Earnings,
|
|
Coupon_code,
|
|
replace(cast(reseller_info::json->'name' as varchar),'\"','') AS Reseller_name,
|
|
replace(cast(reseller_info::json->'email'as varchar),'\"','') AS Reseller_email,
|
|
replace(cast(reseller_info::json->'mobile'as varchar),'\"','') AS Reseller_mobile,
|
|
concat(concat(concat(replace(cast(reseller_info::json->'addressLine1'as varchar),'\"','') || ' ,',replace(cast(reseller_info::json->'addressLine2'as varchar),'\"','') || ', '), concat(replace(cast(reseller_info::json->'city'as varchar),'\"','') || ' ,',replace(cast(reseller_info::json->'pincode'as varchar),'\"','') || ' ,')), replace(cast(reseller_info::json->'province'as varchar),'\"','')) AS Reseller_address,
|
|
replace(cast(reseller_info::json->'store'as varchar),'\"','') AS store_name,
|
|
replace(cast(reseller_info::json->'tierName'as varchar),'\"','') AS tier,
|
|
replace(cast(customer_info::json->'name'as varchar),'\"','') AS customer_name,
|
|
replace(cast(customer_info::json->'email'as varchar),'\"','') AS customer_email,
|
|
concat(concat(concat(replace(cast(customer_info::json->'addressLine1'as varchar),'\"','') || ' ,',replace(cast(customer_info::json->'addressLine2'as varchar),'\"','') || ', '), concat(replace(cast(customer_info::json->'city'as varchar),'\"','') || ' ,',replace(cast(customer_info::json->'pincode'as varchar),'\"','') || ' ,')), replace(cast(customer_info::json->'province'as varchar),'\"','')) AS customer_address,
|
|
Shipping_to AS Order_recipient,
|
|
shipping_address_line1 ,
|
|
shipping_address_line2 ,
|
|
shipping_city ,
|
|
shipping_district ,
|
|
shipping_sub_district ,
|
|
shipping_province ,
|
|
shipping_country_code ,
|
|
shipping_pincode ,
|
|
tracking_url ,
|
|
CASE
|
|
WHEN order_placed_by ='Raena' THEN 'App Order'
|
|
ELSE 'Admin'
|
|
END AS order_placed_by,
|
|
raena_order_management.order.stock_type AS product_stock_type,
|
|
sku AS product_sku,
|
|
oi.name AS product_name,
|
|
product_code,
|
|
date_key,
|
|
product_type,
|
|
batch,
|
|
country,
|
|
quantity,
|
|
wholesale_price ,
|
|
retail_price,
|
|
total_price,
|
|
Total_earnings,
|
|
raena_order_management.order.is_slash_price_enabled ,
|
|
Brand_name,
|
|
category_name,
|
|
product_class,
|
|
source_order_id ,
|
|
marketplace_provider_order_id AS provider_order_id,
|
|
medium,
|
|
marketplace,
|
|
provider,
|
|
C.id as payment_id,
|
|
0 total_cogs,
|
|
'NA' bundle_sku,
|
|
0 sku_level_shipping_fee
|
|
FROM raena_order_management.order
|
|
INNER JOIN raena_order_management.order_item oi ON raena_order_management.order .id=oi.order_id
|
|
left join raena_analytics.payment_order C on raena_order_management.order.id=C.order_id
|
|
left join raena_order_management.channel D on raena_order_management.order.channel_id = D.id
|
|
WHERE raena_order_management.order.created_at <'2021-10-28'
|
|
UNION
|
|
*/
|
|
SELECT A.id AS order_id,
|
|
(A.created_at+interval '7 hours') AS order_date,
|
|
(A.created_at+interval '7 hours') AS id_order_date,
|
|
A.status AS order_status,
|
|
A.Payment_status,
|
|
D.name AS channel_name,
|
|
cast(A.is_bank_transfer AS varchar) AS bank_transfer,
|
|
cast(A.is_campaign AS varchar) AS campaign,
|
|
dynamic_price,
|
|
total_dynamic_and_tier_price AS total_tier_price,
|
|
total_retail_price AS total_MRP,
|
|
tier_discount AS total_tier_discount,
|
|
discount_amount AS coupon_discount,
|
|
GB.payment_amount,
|
|
coalesce(GB.new_shipping_amount_final,shipping_amount) AS Actual_shipping_amount,
|
|
applied_shipping_amount,
|
|
shipping_coupon_discount,
|
|
Earnings,
|
|
A.Coupon_code,
|
|
replace(cast(reseller_info::json->'name' as varchar),'\"','') AS Reseller_name,
|
|
replace(cast(reseller_info::json->'email'as varchar),'\"','') AS Reseller_email,
|
|
replace(cast(reseller_info::json->'mobile'as varchar),'\"','') AS Reseller_mobile,
|
|
concat(concat(concat(replace(cast(reseller_info::json->'addressLine1'as varchar),'\"','') || ' ,',replace(cast(reseller_info::json->'addressLine2'as varchar),'\"','') || ', '), concat(replace(cast(reseller_info::json->'city'as varchar),'\"','') || ' ,',replace(cast(reseller_info::json->'pincode'as varchar),'\"','') || ' ,')), replace(cast(reseller_info::json->'province'as varchar),'\"','')) AS Reseller_address,
|
|
replace(cast(reseller_info::json->'store'as varchar),'\"','') AS store_name,
|
|
replace(cast(reseller_info::json->'tierName'as varchar),'\"','') AS tier,
|
|
replace(cast(customer_info::json->'name'as varchar),'\"','') AS customer_name,
|
|
replace(cast(customer_info::json->'email'as varchar),'\"','') AS customer_email,
|
|
concat(concat(concat(replace(cast(customer_info::json->'addressLine1'as varchar),'\"','') || ' ,',replace(cast(customer_info::json->'addressLine2'as varchar),'\"','') || ', '), concat(replace(cast(customer_info::json->'city'as varchar),'\"','') || ' ,',replace(cast(customer_info::json->'pincode'as varchar),'\"','') || ' ,')), replace(cast(customer_info::json->'province'as varchar),'\"','')) AS customer_address,
|
|
Shipping_to AS Order_recipient,
|
|
shipping_address_line1 ,
|
|
shipping_address_line2 ,
|
|
shipping_city ,
|
|
shipping_district ,
|
|
shipping_sub_district ,
|
|
A.shipping_province ,
|
|
shipping_country_code ,
|
|
shipping_pincode ,
|
|
tracking_url ,
|
|
CASE
|
|
WHEN A.order_placed_by ='reseller' THEN 'App Order'
|
|
ELSE 'Admin'
|
|
END AS order_placed_by,
|
|
A.stock_type AS product_stock_type,
|
|
coalesce(GB.sku,oi.sku) AS product_sku,
|
|
coalesce(gb.sku_name,oi.name) AS product_name,
|
|
product_code,
|
|
date_key,
|
|
coalesce(GB.product_type,oi.product_type) product_type,
|
|
batch,
|
|
country,
|
|
coalesce(GB.quantity,oi.quantity) quantity,
|
|
coalesce(GB.actual_wholesale_price,oi.wholesale_price) wholesale_price,
|
|
coalesce(GB.retail_price,oi.retail_price) retail_price,
|
|
((GB.retail_price*GB.quantity)-(GB.seller_margin*GB.quantity)) total_price,
|
|
Total_earnings,
|
|
A.is_slash_price_enabled ,
|
|
coalesce(gb.brand_name,oi.Brand_name) brand_name,
|
|
coalesce(gb.category_name,oi.category_name) category_name,
|
|
oi.product_class,
|
|
source_order_id ,
|
|
marketplace_provider_order_id AS provider_order_id,
|
|
medium,
|
|
marketplace,
|
|
provider,
|
|
C.id as payment_id,
|
|
(GB.cogs) as total_cogs,
|
|
case when oi.parent_sku is null then 'NA' else oi.parent_sku end bundle_sku,
|
|
new_shipping_amount sku_level_shipping_amount,
|
|
GB.discounted_price*GB.quantity sku_level_payment_amount,
|
|
is_cash_on_delivery,
|
|
payment_provider,
|
|
payment_type,
|
|
payment_category,
|
|
payment_channel
|
|
FROM raena_order_management.order A
|
|
inner join (select AD.* ,
|
|
sum(new_shipping_amount) over(partition by AD.external_id) new_shipping_amount_final ,
|
|
sum(discounted_price*quantity) over(partition by AD.external_id) payment_amount
|
|
from (SELECT A.*,
|
|
discounted_price/count(1) over(partition BY A.external_id,A.sku,A.item_type,A.quantity) new_discounted_price,
|
|
actual_wholesale_price/count(1) over(partition BY A.external_id,A.sku,A.item_type,A.quantity) new_actual_wholesale_price ,
|
|
retail_price/count(1) over(partition BY A.external_id,A.sku,A.item_type,A.quantity) new_retail_price,
|
|
seller_margin/count(1) over(partition BY A.external_id,A.sku,A.item_type,A.quantity) new_seller_margin ,
|
|
cogs/count(1) over(partition BY A.external_id,A.sku,A.item_type,A.quantity) new_cogs,
|
|
(case when (case when item_type like '%Bundle%' then 'Bundle' else 'Product' end )=B.product_class
|
|
then B.new_shipment_amount else C.new_shipment_amount end)/count(1) over(partition BY A.external_id,A.sku,A.item_type) new_shipping_amount
|
|
FROM raena_analytics.gm_dashboard A
|
|
left join raena_analytics.sku_level_shipping_fee_final B
|
|
on a.external_id = B.order_id
|
|
and a.sku = B.sku
|
|
and (case when item_type like '%Bundle%' then 'Bundle' else 'Product' end )=B.product_class
|
|
left join raena_analytics.sku_level_shipping_fee_old_final C
|
|
on a.external_id = C.external_id
|
|
and a.sku = C.sku
|
|
and (case when item_type like '%Bundle%' then 'Bundle' else 'Product' end )=C.product_class) AD
|
|
)GB
|
|
ON A.id = GB.external_id AND GB.sku not like 'BAZ%'
|
|
left JOIN (select A.*,B.sku parent_sku
|
|
from raena_order_management.order_item A
|
|
left join raena_order_management.order_item B on A.parent_item_id = B.id ) oi
|
|
ON GB.external_id=oi.order_id
|
|
AND OI.SKU not like 'BAZ%'
|
|
AND GB.sku=oi.sku
|
|
and GB.parent_sku= oi.parent_sku
|
|
left join raena_analytics.payment_order C on A.id=C.order_id
|
|
left join raena_order_management.channel D on A.channel_id = D.id
|
|
WHERE cast(A.created_at+interval'7 hours' as date)>='2021-10-28';
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.remove_duplicate_from_app_admin;
|
|
|
|
|
|
CREATE TABLE raena_analytics.remove_duplicate_from_app_admin AS
|
|
SELECT A.external_id,
|
|
wholesale_price_gm,
|
|
wholesale_price_app_admin,
|
|
wholesale_price_gm-wholesale_price_app_admin diff
|
|
FROM
|
|
(SELECT external_id ,
|
|
sum(discounted_price*quantity) P1,
|
|
sum((retail_price*quantity)-(seller_margin*quantity)) wholesale_price_gm
|
|
FROM raena_analytics.gm_dashboard gd
|
|
GROUP BY external_id) A
|
|
FULL OUTER JOIN
|
|
(SELECT order_id ,
|
|
payment_amount p2,
|
|
sum(total_price) wholesale_price_app_admin
|
|
FROM raena_analytics.om_app_admin_dump_table oaadt
|
|
GROUP BY order_id,
|
|
payment_amount) B ON A.external_id= B.order_id
|
|
WHERE wholesale_price_gm-wholesale_price_app_admin<>0;
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.dubplicate_record_app_admin;
|
|
|
|
CREATE TABLE raena_analytics.dubplicate_record_app_admin AS
|
|
SELECT DISTINCT *
|
|
FROM raena_analytics.om_app_admin_dump_table oaadt
|
|
WHERE order_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.remove_duplicate_from_app_admin);
|
|
|
|
DELETE
|
|
FROM raena_analytics.om_app_admin_dump_table
|
|
WHERE order_id IN
|
|
(SELECT DISTINCT external_id
|
|
FROM raena_analytics.remove_duplicate_from_app_admin);
|
|
|
|
INSERT INTO raena_analytics.om_app_admin_dump_table
|
|
SELECT *
|
|
FROM raena_analytics.dubplicate_record_app_admin;
|
|
|
|
" > /home/ec2-user/cronjob/postgresql/gm_code/gm_etl_cron.sql
|
|
|
|
psql "host=analytics-db-instance-1.cd7qipz3esdx.ap-southeast-1.rds.amazonaws.com user=dbadmin dbname=analytics port=5432 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/postgresql/gm_code/gm_etl_cron.sql
|