#!/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 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 distinct external_id, transaction_date+interval'7 hours' 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, 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, 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, is_campaign, order_placed_by FROM raena_order_management.order A WHERE payment_status='Paid' 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 public.base_netsuite_stage1_V1; CREATE TABLE public.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 FROM public.order_level_data A LEFT JOIN raena_order_management.sales_sub_order B ON A.external_id = B.order_id LEFT JOIN public.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 public.base_netsuite_stage2_v1_base ; CREATE TABLE public.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 FROM public.base_netsuite_stage1_V1 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 LEFT JOIN (SELECT B.order_id , A.sku , A.created_at created_at , A.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) C ON A.sku = C.sku AND A.external_id = C.order_id LEFT JOIN (SELECT sku, cogs, promo, created_at::date created_at, coalesce(lead(created_at::date-interval'1 day' , 1) over (partition BY sku ORDER BY sku,created_at),CURRENT_DATE+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 public.base_netsuite_stage2_v1; create table public.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 from public.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 public.base_netsuite_stage3_v1 ; CREATE TABLE public.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 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 --retail_price*quantity*discount_amount/sum(retail_price*quantity)over(partition BY A.external_id) -- 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 , ---case when price_type ='NEGOTIATED_PRICE' then --- 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 discount_price --- 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 --- else 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-coalesce(actual_wholesale_price)*quantity end) over(partition by A.external_id )) /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 ---(discount_amount-sum(retail_price*quantity-coalesce(actual_wholesale_price)*quantity) over(partition by A.external_id )) WHEN coupon_applied_on ='Cart' AND discount_price=0 THEN retail_price*quantity*(discount_amount) ---sum(retail_price*quantity-coalesce(actual_wholesale_price)*quantity) over(partition by A.external_id )) /sum(case when retail_price<>0 then retail_price*quantity end )over(partition BY A.external_id) ---end 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 public.base_netsuite_stage2_v1 A ; DROP TABLE IF EXISTS public.base_netsuite_stage4_v1 ; CREATE TABLE public.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 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 cast(final_discount AS decimal(22,2))-cast(seller_margin AS decimal(22,2)) -- END ---case when price_type ='NEGOTIATED_PRICE' then final_discount-seller_margin else final_discount end effective_coupon_discount case when final_discount<>0 then final_discount-seller_margin end as effective_coupon_discount FROM public.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 public.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; --Alter table public.base_netsuite_final --add columns cogs_flag varchar,promo_amount decimal(22,2),actual_wholesale_price decimal(22,2); 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 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 FROM public.order_level_data A LEFT JOIN public.base_netsuite_stage4_v1 B ON A.external_id = B.external_id; Select max(transaction_date) Netsuite_base_date from public.base_netsuite_final; 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 , shipping_to, C.name sku_name, brand_name , product_type , category_name, order_placed_by, 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 WHERE payment_status ='Paid' AND cast(A.created_at+interval'7 hours' AS date) >='$reportDate'; 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 AS 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 , 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') THEN 'EL/PL' END brand_type , Customer_type, BB.gm_target, --CASE -- WHEN B.discount_type = 'Flash' -- AND promo_amount IS NOT NULL THEN 'Flash' -- WHEN B.discount_type = 'Negotiated' -- AND promo_amount IS NOT NULL THEN 'Negotiated' -- WHEN promo_amount IS NOT NULL THEN 'Brand' -- END B.discount_type discount_promo_type , promo_amount, actual_wholesale_price, effective_wholesale_price, coalesce(cogs_type,CASE WHEN A.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 FROM public.gm_dimensions_stage1 A INNER JOIN (select distinct * from public.base_netsuite_final) B ON A.external_id =B.external_id AND A.sku=B.product_sku LEFT JOIN (SELECT B.order_id , A.sku , A.created_at created_at , A.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) C ON A.sku = C.sku AND A.external_id = C.order_id LEFT JOIN (SELECT sku, cogs, promo, (created_at)::date created_at, coalesce(lead(created_at::date-interval'1 day' , 1) over (partition BY sku ORDER BY sku,created_at),CURRENT_DATE+interval'1 day')::date last_date FROM raena_catalog_management.cogs_audit ca ORDER BY 1 , 4) D ON A.sku = D.sku AND B.transaction_date::date BETWEEN D.created_at AND last_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 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 public.GM_dashboard; DROP TABLE IF EXISTS public.GM_dashboard_v1; CREATE TABLE public.GM_dashboard_v1 AS SELECT * FROM public.GM_dashboard WHERE external_id IN (SELECT DISTINCT external_id FROM public.gm_dimensions_stage1); DELETE FROM public.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 public.gm_dimensions_stage1); INSERT INTO public.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 FROM public.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 public.gm_dimensions_stage1); 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,'province',TRUE) reseller_province, 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, shipping_coupon_discount 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+interval'7 hours' AS date) >='$reportDate' ; DELETE FROM public.business_report WHERE external_id IN (SELECT DISTINCT external_id FROM public.business_dimensions_stage1); Insert into public.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, 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 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') THEN 'EL/PL' END brand_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 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 public.business_report_v1; CREATE TABLE public.business_report_v1 AS SELECT * FROM public.business_report WHERE external_id IN (SELECT DISTINCT external_id FROM public.business_dimensions_stage1); DELETE FROM public.business_report USING (SELECT DISTINCT sku, sku_name, external_id FROM public.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 public.business_report.external_id = B.external_id AND public.business_report.sku=B.sku AND public.business_report.external_id IN (SELECT DISTINCT external_id FROM public.business_dimensions_stage1); INSERT INTO public.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 FROM (SELECT public.business_report_v1.* FROM public.business_report_v1 INNER JOIN (SELECT DISTINCT sku, sku_name, external_id FROM public.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 public.business_report_v1.external_id = B.external_id AND public.business_report_v1.sku=B.sku) A WHERE external_id IN (SELECT DISTINCT external_id FROM public.business_dimensions_stage1); Select max(created_date) business_date from public.business_report; drop table if exists om_user_ist_install_table; create table om_user_ist_install_table as select email,app_install_date from ( select distinct email,app_install_date,row_number() over (partition by email order by app_install_date) as R from ( select distinct email,app_install_date from om_clevertap_install_jan_may where email notnull and email!='' union select distinct email,cast(created_at as date) as created_at from raena_user_management.user u ) ) where R=1; select 1 ; DELETE FROM public.OM_GM_DB_Product_category WHERE external_Id IN (SELECT DISTINCT external_id FROM public.gm_dimensions_stage1); INSERT INTO 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' else 'Na' end AS New_existing_flag, is_campaign, dsf_flag, cogs_flag 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 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))/(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 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 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 user_type_table flag ON replace(base.reseller_mobile,'+','')=flag.reseller_mobile; Select max(transaction_date) om_gm_db_product_date from public.OM_GM_DB_Product_category; --Business Dashboard DELETE FROM public.GM_GROWTH_TAB1 WHERE created_date >='$reportDate'; /* INSERT INTO public.GM_GROWTH_TAB1 select base.*, 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 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 from (SELECT DISTINCT coalesce(a.tier,'NA') as tier, 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,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, 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) >= '$reportDate' AND channel_id IS NOT NULL) b ON a.external_id=b.id WHERE payment_status='Paid' AND created_date >= '$reportDate' UNION 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, 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) >= '$reportDate' AND channel_id IS NOT NULL) b ON a.external_id=b.id WHERE payment_status='Paid' AND created_date >= '$reportDate') 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, discount_promo_type, reseller_province, is_campaign, dsf_flag, brand_type ) base left join user_type_table flag on replace(base.reseller_mobile,'+','')=flag.reseller_mobile; */ INSERT INTO public.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 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 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, 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) >= '$reportDate' AND channel_id IS NOT NULL) b ON a.external_id=b.id WHERE payment_status='Paid' AND created_date >= '$reportDate') 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 ) base left join user_type_table flag on replace(base.reseller_mobile,'+','')=flag.reseller_mobile; Select max(created_date) gm_growth_date from public.GM_GROWTH_TAB1; --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; DROP TABLE IF EXISTS public.GM_GROWTH_TAB3; CREATE TABLE public.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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 ) 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 ) 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 ) 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 ) 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 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, sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator from public.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 ) ORDER BY 3; drop table shipping_fee_base_table; create table 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 public.OM_Logistic_final_shipping C on A.sales_sub_order_shipment_id = C.reference_id group by A.order_id; DROP TABLE public.shipping_fee_sku_base_stage1 ; CREATE TABLE public.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 OM_Logistic_final_shipping D ON A.sales_sub_order_shipment_id=D.reference_id ; DROP TABLE public.weight_sku_table; CREATE TABLE public.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 public.shipping_fee_sku_base_stage2; CREATE TABLE public.shipping_fee_sku_base_stage2 AS SELECT A.*, B.weight*Quantity sku_weight, CASE WHEN (B.weight*Quantity)<1.3 THEN 1 WHEN (B.weight*Quantity)>=1.3 AND (ABS((B.weight*Quantity)) - FLOOR(ABS((B.weight*Quantity)))) BETWEEN 0.3 AND 0.999999 THEN FLOOR((B.weight*Quantity))+1 ELSE FLOOR((B.weight*Quantity)) END AS final_weight FROM public.shipping_fee_sku_base_stage1 A LEFT JOIN public.weight_sku_table B ON A.sku = B.sku ; DROP TABLE public.shipping_fee_sku_base_stage3; CREATE TABLE public.shipping_fee_sku_base_stage3 AS SELECT *, (sku_weight*shipping_amount)/sum(sku_weight) over(partition BY sales_sub_order_shipment_id) new_shipment_amount FROM public.shipping_fee_sku_base_stage2; DROP TABLE sku_level_shipping_fee_final; CREATE TABLE public.sku_level_shipping_fee_final AS SELECT order_id , sku, product_class , sum(new_shipment_amount) new_shipment_amount FROM public.shipping_fee_sku_base_stage3 GROUP BY 1, 2, 3; DROP TABLE public.shipping_fee_sku_base_old_stage1; CREATE TABLE public.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 gm_dashboard A LEFT JOIN public.weight_sku_table B ON A.sku = B.sku; DROP TABLE public.shipping_fee_sku_base_old_stage2; CREATE TABLE public.shipping_fee_sku_base_old_stage2 AS SELECT *, (sku_weight*shipping_cost)/sum(sku_weight) over(partition BY external_id) new_shipment_amount FROM public.shipping_fee_sku_base_old_stage1 ; DROP TABLE public.sku_level_shipping_fee_old_final; CREATE TABLE public.sku_level_shipping_fee_old_final AS SELECT external_id , sku, product_class , sum(new_shipment_amount) new_shipment_amount FROM public.shipping_fee_sku_base_old_stage2 GROUP BY 1, 2, 3; DROP TABLE IF EXISTS public.payment_order_stage; CREATE TABLE public.payment_order_stage AS WITH NS AS (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25) SELECT id, max(created_at) created_at, replace(cast(substring(cast(TRIM(SPLIT_PART(B.order_ids , ',', NS.n))AS varchar),2,length(cast(TRIM(SPLIT_PART(B.order_ids , ',', NS.n))AS varchar))-2) AS varchar),'\"','') AS order_id FROM NS INNER JOIN raena_order_management.payment B ON NS.n <= REGEXP_COUNT(B.order_ids , ',') + 1 GROUP BY 1, 3; DROP TABLE IF EXISTS public.payment_order; CREATE TABLE public.payment_order AS SELECT A.id , A.created_at, A.order_id FROM public.payment_order_stage A INNER JOIN (SELECT order_id , max(created_at) max_created_at FROM public.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 public.payment_order; drop table if exists om_app_admin_dump_table; CREATE TABLE om_app_admin_dump_table AS SELECT raena_order_management.order.id AS order_id, dateadd(hour,7,raena_order_management.order.created_at) AS order_date, dateadd(hour,7,raena_order_management.order.created_at) AS id_order_date, status AS order_status, Payment_status, order_store AS sales_channel, 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, json_extract_path_text(reseller_info, 'name') AS Reseller_name, json_extract_path_text(reseller_info, 'email') AS Reseller_email, json_extract_path_text(reseller_info, 'mobile') AS Reseller_mobile, concat(concat(concat(json_extract_path_text(reseller_info, 'addressLine1') || ' ,',json_extract_path_text(reseller_info, 'addressLine2') || ', '), concat(json_extract_path_text(reseller_info, 'city') || ' ,',json_extract_path_text(reseller_info, 'pincode') || ' ,')), json_extract_path_text(reseller_info, 'province')) AS Reseller_address, json_extract_path_text(reseller_info, 'store') AS store_name, json_extract_path_text(reseller_info,'tierName',TRUE) AS tier, json_extract_path_text(customer_info, 'name') AS customer_name, json_extract_path_text(customer_info, 'email') AS customer_email, concat(concat(concat(json_extract_path_text(customer_info, 'addressLine1') || ' ,',json_extract_path_text(customer_info, 'addressLine2') || ', '), concat(json_extract_path_text(customer_info, 'city') || ' ,',json_extract_path_text(customer_info, 'pincode') || ' ,')), json_extract_path_text(customer_info, 'province')) 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 FROM raena_order_management.order INNER JOIN raena_order_management.order_item oi ON raena_order_management.order .id=oi.order_id left join public.payment_order C on raena_order_management.order.id=C.order_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, A.order_store AS sales_channel, 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, sum(GB.new_discounted_price*gb.quantity) over(partition by A.id) payment_amount, shipping_amount AS Actual_shipping_amount, applied_shipping_amount, shipping_coupon_discount, Earnings, A.Coupon_code, json_extract_path_text(reseller_info, 'name') AS Reseller_name, json_extract_path_text(reseller_info, 'email') AS Reseller_email, json_extract_path_text(reseller_info, 'mobile') AS Reseller_mobile, concat(concat(concat(json_extract_path_text(reseller_info, 'addressLine1') || ' ,',json_extract_path_text(reseller_info, 'addressLine2') || ', '), concat(json_extract_path_text(reseller_info, 'city') || ' ,',json_extract_path_text(reseller_info, 'pincode') || ' ,')), json_extract_path_text(reseller_info, 'province')) AS Reseller_address, json_extract_path_text(reseller_info, 'store') AS store_name, json_extract_path_text(reseller_info,'tierName',TRUE) AS tier, json_extract_path_text(customer_info, 'name') AS customer_name, json_extract_path_text(customer_info, 'email') AS customer_email, concat(concat(concat(json_extract_path_text(customer_info, 'addressLine1') || ' ,',json_extract_path_text(customer_info, 'addressLine2') || ', '), concat(json_extract_path_text(customer_info, 'city') || ' ,',json_extract_path_text(customer_info, 'pincode') || ' ,')), json_extract_path_text(customer_info, 'province')) 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 ='Raena' 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), batch, country, coalesce(GB.quantity,oi.quantity), coalesce(GB.new_actual_wholesale_price,oi.wholesale_price) , coalesce(GB.new_retail_price,oi.retail_price), coalesce(((GB.new_retail_price*GB.quantity)-(GB.new_seller_margin*GB.quantity)),total_price), Total_earnings, A.is_slash_price_enabled , coalesce(gb.brand_name,oi.Brand_name), coalesce(gb.category_name,oi.category_name), oi.product_class, source_order_id , marketplace_provider_order_id AS provider_order_id, medium, marketplace, provider, C.id as payment_id FROM raena_order_management.order A left JOIN raena_order_management.order_item oi ON A.id=oi.order_id AND OI.SKU not like 'BAZ%' left JOIN (select *,discounted_price/count(1) over(partition by external_id,sku,item_type) new_discounted_price, actual_wholesale_price/count(1) over(partition by external_id,sku,item_type) new_actual_wholesale_price , retail_price/count(1) over(partition by external_id,sku,item_type) new_retail_price, seller_margin/count(1) over(partition by external_id,sku,item_type) new_seller_margin from public.gm_dashboard) GB ON oi.order_id = GB.external_id AND oi.sku = GB.sku and GB.sku not like 'BAZ%' left join public.payment_order C on A.id=C.order_id WHERE cast(A.created_at+interval '7 hours' as date) >='2021-10-28' ; " > /home/ec2-user/cronjob/Gm_dashboard/Final_GM_v2.sql psql "host=raen-prd-sg-redshift-cluster.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/Gm_dashboard/Final_GM_v2.sql