148 lines
6.2 KiB
Plaintext
148 lines
6.2 KiB
Plaintext
|
#!/bin/bash
|
||
|
|
|
||
|
|
echo "
|
||
|
|
|
||
|
|
--------------------------Revenue Leakage -------------------------
|
||
|
|
|
||
|
|
drop table if exists om_brand_type_last_month;
|
||
|
|
|
||
|
|
|
||
|
|
create table om_brand_type_last_month
|
||
|
|
as
|
||
|
|
select distinct brand_name,
|
||
|
|
case when brand_name in ('HISTOIRE NATURELLE','INGRID','W DRESSROOM','FORENCOS','DEWYCEL','GLUTANEX','BELLFLOWER','ONE THING','BEAUSTA')
|
||
|
|
then 'EL\/PL' else 'NA' end as "EL\/PL Type",
|
||
|
|
case
|
||
|
|
when After_discount_GM <= '5.00' then 'offender 1'
|
||
|
|
when After_discount_GM between 5 and 10 then 'offender 2'
|
||
|
|
when After_discount_GM between 10 and 20 then 'DDB1'
|
||
|
|
when After_discount_GM between 20 and 30 then 'DDB2'
|
||
|
|
when After_discount_GM > 30 then 'DDB3' else 'NA' end as brand_type
|
||
|
|
from (
|
||
|
|
select brand_name ,
|
||
|
|
cast((sum((quantity*discounted_price)-(cogs*quantity))*100/sum(quantity*discounted_price)) as decimal(10,4)) as After_discount_GM
|
||
|
|
from OM_GM_DB_Product_category
|
||
|
|
where date_part('month',transaction_date)=EXTRACT(month FROM CURRENT_DATE - '1 month'::interval) and
|
||
|
|
date_part('year',transaction_date)=EXTRACT(year FROM CURRENT_DATE - '1 month'::interval)
|
||
|
|
group by 1
|
||
|
|
) order by 2;
|
||
|
|
|
||
|
|
drop table if exists om_seller_type_last_month;
|
||
|
|
|
||
|
|
|
||
|
|
create table om_seller_type_last_month
|
||
|
|
as
|
||
|
|
select reseller_email,case when After_discount_GM between 0 and 10.00 then 'Single digit Seller' when After_discount_GM >= 10.00 then 'Double Digit Seller' else 'NA' end as Seller_type
|
||
|
|
from (
|
||
|
|
select reseller_email,
|
||
|
|
cast((sum((quantity*discounted_price)-(cogs*quantity))*100/sum(quantity*discounted_price)) as decimal(10,4)) as After_discount_GM
|
||
|
|
from OM_GM_DB_Product_category
|
||
|
|
where date_part('month',transaction_date)=EXTRACT(month FROM CURRENT_DATE - '1 month'::interval) and
|
||
|
|
date_part('year',transaction_date)=EXTRACT(year FROM CURRENT_DATE - '1 month'::interval)
|
||
|
|
and reseller_email notnull
|
||
|
|
group by 1
|
||
|
|
);
|
||
|
|
|
||
|
|
drop table if exists om_seller_type_last_month_10M;
|
||
|
|
|
||
|
|
|
||
|
|
create table om_seller_type_last_month_10M
|
||
|
|
as
|
||
|
|
select distinct reseller_email,case when Last_month_rev > 10000000 then 'REV GT 10M' else 'REV LT 10M' end as Last_Month_Rev_Flag
|
||
|
|
from (
|
||
|
|
select reseller_email,sum(Payment_Price) Last_month_rev
|
||
|
|
from GM_GROWTH_TAB1
|
||
|
|
where date_part('month',created_date)=EXTRACT(month FROM CURRENT_DATE - '1 month'::interval) and
|
||
|
|
date_part('year',created_date)=EXTRACT(year FROM CURRENT_DATE - '1 month'::interval) and GM_GROWTH_TAB1.brand_name!='All'
|
||
|
|
and reseller_email notnull
|
||
|
|
group by 1
|
||
|
|
) ;
|
||
|
|
|
||
|
|
drop table if exists om_sku_weighted_avg;
|
||
|
|
|
||
|
|
create table om_sku_weighted_avg
|
||
|
|
as
|
||
|
|
select sku,sum(Numerator)/sum(quantity) as Weighted_avg
|
||
|
|
from (
|
||
|
|
select sku,(effective_wholesale_price)*quantity as Numerator,quantity,
|
||
|
|
row_number() over (partition by sku order by created_date desc) as R,cast(created_date as date)
|
||
|
|
from (
|
||
|
|
select sso.product_class,parent_sku as sku,sso.effective_wholesale_price,sso.quantity,cast(sso.created_at as date) as created_date
|
||
|
|
from raena_order_management.sales_sub_order sso
|
||
|
|
where price_type='NEGOTIATED_PRICE' --and sku='AVS019'
|
||
|
|
)
|
||
|
|
) where R<=5
|
||
|
|
group by 1
|
||
|
|
order by 1;
|
||
|
|
|
||
|
|
|
||
|
|
drop table if exists public.om_dormant_sellers_filter_base;
|
||
|
|
|
||
|
|
|
||
|
|
create table public.om_dormant_sellers_filter_base
|
||
|
|
as
|
||
|
|
select distinct a.reseller_email,flag_3,flag_2,flag_1,flag_0
|
||
|
|
from (
|
||
|
|
select distinct "email\ id" as reseller_email
|
||
|
|
from AM_REVENUE_LEAKAGE_LIST
|
||
|
|
inner join raena_user_management.user b on AM_REVENUE_LEAKAGE_LIST."email\ id" = b.email
|
||
|
|
left join GM_GROWTH_TAB1 on GM_GROWTH_TAB1.reseller_email=AM_REVENUE_LEAKAGE_LIST."email\ id"
|
||
|
|
) a
|
||
|
|
left join (
|
||
|
|
select distinct reseller_email,'MTD' as flag_0
|
||
|
|
from GM_GROWTH_TAB1
|
||
|
|
where cast(created_date as date)>= cast(date_add('month', 0, date_trunc('month', current_date)) as date) and GM_GROWTH_TAB1.brand_name!='All'
|
||
|
|
order by 1
|
||
|
|
) h on a.reseller_email=h.reseller_email
|
||
|
|
left join (
|
||
|
|
select distinct reseller_email,'one_month' as flag_1
|
||
|
|
from GM_GROWTH_TAB1
|
||
|
|
where cast(created_date as date)>= cast(date_add('month', -1, date_trunc('month', current_date)) as date)
|
||
|
|
and cast(created_date as date)<= cast(date_add('month', 0, date_trunc('month', current_date)) as date) and GM_GROWTH_TAB1.brand_name!='All'
|
||
|
|
order by 1
|
||
|
|
) b on a.reseller_email=b.reseller_email
|
||
|
|
left join (
|
||
|
|
select distinct reseller_email,'2nd_month' as flag_2
|
||
|
|
from GM_GROWTH_TAB1
|
||
|
|
where cast(created_date as date)>= cast(date_add('month', -3, date_trunc('month', current_date)) as date)
|
||
|
|
and cast(created_date as date)<= cast(date_add('month', 0, date_trunc('month', current_date)) as date) and GM_GROWTH_TAB1.brand_name!='All'
|
||
|
|
order by 1
|
||
|
|
) c on a.reseller_email=c.reseller_email
|
||
|
|
left join (
|
||
|
|
select distinct reseller_email,'3rd_month' as flag_3
|
||
|
|
from GM_GROWTH_TAB1
|
||
|
|
where cast(created_date as date)>= cast(date_add('month', -6, date_trunc('month', current_date)) as date)
|
||
|
|
and cast(created_date as date)<= cast(date_add('month', 0, date_trunc('month', current_date)) as date) and GM_GROWTH_TAB1.brand_name!='All'
|
||
|
|
order by 1
|
||
|
|
) d on a.reseller_email=d.reseller_email;
|
||
|
|
|
||
|
|
drop table if exists public.om_dormant_sellers_filter;
|
||
|
|
|
||
|
|
create table public.om_dormant_sellers_filter
|
||
|
|
as
|
||
|
|
select distinct reseller_email,Dornant_flag
|
||
|
|
from (
|
||
|
|
select distinct reseller_email, case when flag_3 is null then 'M-6' end as Dornant_flag
|
||
|
|
from public.om_dormant_sellers_filter_base
|
||
|
|
union
|
||
|
|
select distinct reseller_email, case when flag_2 is null then 'M-3' end as Dornant_flag
|
||
|
|
from public.om_dormant_sellers_filter_base
|
||
|
|
union
|
||
|
|
select distinct reseller_email, case when flag_1 is null then 'M-1' end as Dornant_flag
|
||
|
|
from public.om_dormant_sellers_filter_base
|
||
|
|
union
|
||
|
|
select distinct reseller_email, case when flag_0 is null then 'MTD' end as Dornant_flag
|
||
|
|
from public.om_dormant_sellers_filter_base
|
||
|
|
union
|
||
|
|
select distinct reseller_email, case when flag_3 is null and flag_2 is null and flag_1 is null and flag_0 is null then 'Never Transacted in Last 6 Months' end as Dornant_flag
|
||
|
|
from public.om_dormant_sellers_filter_base
|
||
|
|
)
|
||
|
|
--where reseller_email='ziyyags@gmail.com'
|
||
|
|
order by 1;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/AM_ACQ/AM_dashboard.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/AM_ACQ/AM_dashboard.sql > AM_Dashboard.log
|