raena_analytics_scripts/redshift/sql_code/am_dashboard.sql

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;