223 lines
6.1 KiB
Bash
223 lines
6.1 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.Am_dashbaord_base1_table;
|
|
|
|
|
|
CREATE TABLE raena_analytics.Am_dashbaord_base1_table AS
|
|
SELECT A.external_id ,
|
|
transaction_date::date transaction_date,
|
|
reseller_email,
|
|
reseller_id,
|
|
customer_type ,
|
|
coupon_code,
|
|
brand_name ,
|
|
A.sku,
|
|
order_placed_by ,
|
|
retail_price ,
|
|
seller_margin ,
|
|
sum(discounted_price) discounted_price,
|
|
SUM(quantity) quantity ,
|
|
cogs,
|
|
sum(CASE WHEN 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 THEN B.new_shipment_amount ELSE C.new_shipment_amount END) Shipping_amount,
|
|
shipping_province,
|
|
province
|
|
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
|
|
LEFT JOIN
|
|
(SELECT id ,
|
|
province
|
|
FROM raena_user_management.user
|
|
WHERE province IS NOT NULL) D ON A.reseller_id = cast(D.id AS varchar)
|
|
WHERE transaction_date::date BETWEEN CURRENT_DATE-interval'6 months' AND CURRENT_DATE
|
|
AND reseller_id IS NOT NULL
|
|
GROUP BY A.external_id ,
|
|
transaction_date::date,
|
|
reseller_id,
|
|
reseller_email,
|
|
customer_type ,
|
|
coupon_code,
|
|
brand_name ,
|
|
A.sku,
|
|
order_placed_by ,
|
|
coupon_code ,
|
|
retail_price ,
|
|
seller_margin,
|
|
cogs,
|
|
shipping_province,
|
|
province;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.brand_type_base_table ;
|
|
|
|
|
|
CREATE TABLE raena_analytics.brand_type_base_table AS
|
|
SELECT brand_name,
|
|
CASE
|
|
WHEN post_discount_gm<5 THEN 'Offender1'
|
|
WHEN post_discount_gm BETWEEN 5 AND 9.99999 THEN 'Offender2'
|
|
WHEN post_discount_gm BETWEEN 10 AND 19.99999 THEN 'DDB1'
|
|
WHEN post_discount_gm BETWEEN 20 AND 29.99999 THEN 'DDB2'
|
|
WHEN post_discount_gm>30 THEN 'DDB3'
|
|
ELSE 'EL/PL'
|
|
END brand_type,
|
|
CASE
|
|
WHEN post_discount_gm <10 THEN 'SDS'
|
|
ELSE 'DDS'
|
|
END seller_type
|
|
FROM
|
|
(SELECT brand_name ,
|
|
cast((sum((discounted_price*quantity)-(cogs*quantity))*100)/sum(CASE WHEN discounted_price<>0 THEN discounted_price*quantity END) AS decimal(10,4)) post_discount_gm
|
|
FROM raena_analytics.Am_dashbaord_base1_table
|
|
GROUP BY 1) A;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.Am_dashbaord_base2_table;
|
|
|
|
|
|
CREATE TABLE raena_analytics.Am_dashbaord_base2_table AS
|
|
SELECT A.* ,
|
|
B.brand_type,
|
|
B.seller_type,
|
|
C.max_td_date,
|
|
concat('M-',cast((date_part('year',CURRENT_DATE)-date_part('year',max_td_date))*12+(date_part('Month',CURRENT_DATE)-date_part('Month',max_td_date)) AS varchar)) dom
|
|
FROM raena_analytics.Am_dashbaord_base1_table A
|
|
LEFT JOIN raena_analytics.brand_type_base_table B ON A.brand_name = B.brand_name
|
|
LEFT JOIN
|
|
(SELECT reseller_id,
|
|
max(transaction_date::date) max_td_date
|
|
FROM raena_analytics.gm_dashboard gd
|
|
GROUP BY 1) C ON A.reseller_id = C.reseller_id ;
|
|
|
|
|
|
DROP TABLE if exists raena_analytics.dormant_reseller_base;
|
|
|
|
|
|
CREATE TABLE raena_analytics.dormant_reseller_base AS
|
|
SELECT DISTINCT reseller_id,
|
|
CASE
|
|
WHEN Dom = 'M-0' THEN 0
|
|
END \"M-0\" ,
|
|
CASE
|
|
WHEN Dom = 'M-1' THEN 1
|
|
END \"M-1\",
|
|
CASE
|
|
WHEN Dom = 'M-2' THEN 1
|
|
END \"M-2\",
|
|
CASE
|
|
WHEN Dom = 'M-3' THEN 1
|
|
END \"M-3\",
|
|
CASE
|
|
WHEN Dom = 'M-4' THEN 1
|
|
END \"M-4\",
|
|
CASE
|
|
WHEN Dom = 'M-5' THEN 1
|
|
END \"M-5\" ,
|
|
CASE
|
|
WHEN Dom = 'M-6' THEN 1
|
|
END \"M-6\"
|
|
FROM raena_analytics.Am_dashbaord_base2_table;
|
|
|
|
|
|
UPDATE raena_analytics.dormant_reseller_base
|
|
SET \"M-5\" = 1
|
|
WHERE \"M-6\" = 1;
|
|
|
|
|
|
UPDATE raena_analytics.dormant_reseller_base
|
|
SET \"M-4\" = 1
|
|
WHERE \"M-5\" = 1;
|
|
|
|
|
|
UPDATE raena_analytics.dormant_reseller_base
|
|
SET \"M-3\" = 1
|
|
WHERE \"M-4\" = 1;
|
|
|
|
|
|
UPDATE raena_analytics.dormant_reseller_base
|
|
SET \"M-2\" = 1
|
|
WHERE \"M-3\" = 1;
|
|
|
|
|
|
UPDATE raena_analytics.dormant_reseller_base
|
|
SET \"M-1\" = 1
|
|
WHERE \"M-2\" = 1;
|
|
|
|
|
|
UPDATE raena_analytics.dormant_reseller_base
|
|
SET \"M-0\" = 1
|
|
WHERE \"M-1\" = 1;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.final_dormant_base;
|
|
|
|
|
|
CREATE TABLE raena_analytics.final_dormant_base AS
|
|
SELECT reseller_id,
|
|
'M-0' dormant
|
|
FROM raena_analytics.dormant_reseller_base
|
|
WHERE \"M-0\" =1
|
|
UNION ALL
|
|
SELECT reseller_id,
|
|
'M-1' dormant
|
|
FROM raena_analytics.dormant_reseller_base
|
|
WHERE \"M-1\" =1
|
|
UNION ALL
|
|
SELECT reseller_id,
|
|
'M-2' dormant
|
|
FROM raena_analytics.dormant_reseller_base
|
|
WHERE \"M-2\" =1
|
|
UNION ALL
|
|
SELECT reseller_id,
|
|
'M-3' dormant
|
|
FROM raena_analytics.dormant_reseller_base
|
|
WHERE \"M-3\" =1
|
|
UNION ALL
|
|
SELECT reseller_id,
|
|
'M-4' dormant
|
|
FROM raena_analytics.dormant_reseller_base
|
|
WHERE \"M-4\" =1
|
|
UNION ALL
|
|
SELECT reseller_id,
|
|
'M-5' dormant
|
|
FROM raena_analytics.dormant_reseller_base
|
|
WHERE \"M-5\" =1
|
|
UNION ALL
|
|
SELECT reseller_id,
|
|
'M-6' dormant
|
|
FROM raena_analytics.dormant_reseller_base
|
|
WHERE \"M-6\" =1;
|
|
|
|
|
|
" > /home/ec2-user/cronjob/redshift/sql_code/am_dashboard.sql
|
|
|
|
psql "host=redshift-cluster-1.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/redshift/sql_code/am_dashboard.sql
|
|
|