raena_analytics_scripts/redshift/Product_metrics_2.sh

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 raena_analytics.daily_recommendation_ingredients_split;

        create table raena_analytics.daily_recommendation_ingredients_split
        as
        select reseller_email ,sku,replace(replace(replace("ingredients",'{',''),'}',''),'\"','') Ingredients,score,after_discount
         from raena_recommendation_engine.daily_recommendation_ingredients;

drop table raena_analytics.daily_recommendation_concerns_split;

 create table raena_analytics.daily_recommendation_concerns_split
        as
        select reseller_email ,sku,replace(replace(replace("Concerns" ,'{',''),'}',''),'\"','') Ingredients,score,after_discount
     from raena_recommendation_engine.daily_recommendation_concerns drc ;


DROP TABLE raena_analytics.product_metrics_calendar;

CREATE TABLE raena_analytics.product_metrics_calendar AS
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'New' user_type,
                '<=2M'bucket
FROM raena_order_management.order
union
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'New' user_type,
                '>10M'bucket
FROM raena_order_management.order
union
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'New' user_type,
                '2M-10M'bucket
FROM raena_order_management.order
union
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'New' user_type,
                'Na'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Activation' user_type,
                '<=2M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Activation' user_type,
                '>10M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Activation' user_type,
                '2M-10M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Activation' user_type,
                'Na'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Existing' user_type,
                '<=2M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Existing' user_type,
                '>10M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Existing' user_type,
                '2M-10M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Existing' user_type,
                'Na'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Na' user_type,
                '<=2M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Na' user_type,
                '>10M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Na' user_type,
                '2M-10M'bucket
FROM raena_order_management.order
UNION
SELECT DISTINCT CREATED_AT::date transaction_date ,
                'Na' user_type,
                'Na'bucket
FROM raena_order_management.order;

DROP TABLE raena_analytics.cohort_level_reseller;


CREATE TABLE raena_analytics.cohort_level_reseller AS
SELECT reseller_id ,
       CASE
           WHEN max_payment_amount BETWEEN 0 AND 2000000 THEN '<=2M'
           WHEN max_payment_amount BETWEEN 2000000 AND 10000000 THEN '2M-10M'
           WHEN max_payment_amount > 10000000 THEN '>10M'
       END Bucket
FROM
  (SELECT reseller_id ,
          max(payment_amount) max_payment_amount
          FROM
     (SELECT reseller_id ,
             date_trunc('Month',created_at)::date trnsaction_mount,
             sum(payment_amount)payment_amount
      FROM raena_order_management.order
      GROUP BY 1,
               2) A
   GROUP BY 1) B ;


drop table if exists raena_analytics.order_base_for_user_type;

create table raena_analytics.order_base_for_user_type
as
SELECT replace(json_extract_path_text(reseller_info,'mobile',true),'+','') reseller_mobile,
          min(created_at) First_transaction_date
   FROM raena_order_management.order
   WHERE payment_status = 'Paid' or id like 'PL%'
   GROUP BY 1;

DROP TABLE IF EXISTS raena_analytics.user_type_table ;


CREATE TABLE raena_analytics.user_type_table AS
SELECT DISTINCT profile_phone reseller_mobile,
                First_transaction_date,
                min_created_date first_install_date
FROM raena_analytics.order_base_for_user_type  A
INNER JOIN
  (SELECT profile_phone,
          min(created_at) min_created_date
   FROM
     (SELECT profile_phone,
             MIN(cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) created_at
      FROM clevertap.app_installed
      GROUP BY profile_phone
      UNION 
      SELECT replace(mobile,'+','') profile_phone,
                   MIN(cast(created_at AS date)) AS created_at
      FROM raena_user_management.user u
      GROUP BY 1) A
   GROUP BY 1) B ON A.reseller_mobile=B.profile_phone;


DROP TABLE IF EXISTS raena_analytics.total_registered_user;


CREATE TABLE raena_analytics.total_registered_user AS
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       bucket,
       transaction_date,
       Count(DISTINCT id) Total_users
FROM  (SELECT distinct id ,replace(mobile,'+','') mobile,
          created_at::date transaction_date
   FROM raena_user_management.user) A
LEFT JOIN raena_analytics.user_type_table B ON A.mobile = B.reseller_mobile
left join raena_analytics.cohort_level_reseller on  A.id  = cohort_level_reseller.reseller_id  
GROUP BY 1,
         2,3;



DROP TABLE IF EXISTS raena_analytics.total_uninstalled;


CREATE TABLE raena_analytics.total_uninstalled AS
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       transaction_date,
       bucket,
       count(1) total_event ,
       Count(DISTINCT A.profile_phone) Total_users
FROM  (SELECT profile_objectid,
          CASE
              WHEN profile_phone<>'' THEN profile_phone
          END profile_phone,
          profile_email,
          profile_platform,
          eventname,
          cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
   FROM clevertap.app_uninstalled) A
LEFT JOIN raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
left join raena_user_management.user C on A.profile_phone = replace(C.mobile,'+','') 
left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
GROUP BY 1,
         2,3;


DROP TABLE IF EXISTS raena_analytics.total_installed;


CREATE TABLE raena_analytics.total_installed AS
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       transaction_date,
       bucket,
       count(1) total_event ,
       Count(DISTINCT A.profile_phone) Total_users
FROM
  (SELECT profile_objectid,
          profile_phone,
          profile_email,
          profile_platform,
          eventname,
          cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
   FROM clevertap.app_installed) A
LEFT JOIN raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
left join raena_user_management.user C on A.profile_phone = replace(C.mobile,'+','') 
left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
GROUP BY 1,
         2,3;

  DROP TABLE raena_analytics.Active_user;


CREATE TABLE raena_analytics.Active_user AS
SELECT date_trunc('Month',A.transaction_date)::date event_created_date,
       date_trunc('Month',first_install_date)::date install_date,
       date_trunc('Month',First_transaction_date)::date first_transaction_date,
       CASE
           WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',coalesce(first_install_date,D.created_at::date))::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND (date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date
                     OR First_transaction_date IS NULL) THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
           WHEN date_trunc('Month',first_install_date)::date IS NULL
                AND first_transaction_date IS NULL THEN 'New'
           WHEN date_trunc('Month',first_install_date)::date IS NULL
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',first_install_date)::date IS NULL
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
           ELSE 'Na'
       END user_type,
       transaction_date,
       count(distinct profile_phone) total_event ,
       A.profile_phone,
       A.profile_objectid,
       bucket
FROM
  (SELECT profile_objectid,
          profile_phone,
          profile_email,
          profile_platform,
          eventname,
          cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
   FROM clevertap.app_launched) A
LEFT JOIN raena_analytics.order_base_for_user_type B ON A.profile_phone = B.reseller_mobile
LEFT JOIN
  (SELECT profile_objectid,
          MIN(cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) first_install_date
   FROM clevertap.app_installed
   GROUP BY profile_objectid) C ON A.profile_objectid= C.profile_objectid
LEFT JOIN raena_user_management.user D ON A.profile_phone = replace(D.mobile,'+','')
left join raena_analytics.cohort_level_reseller on  D.id  = cohort_level_reseller.reseller_id  
GROUP BY 1,
         2,
         3,
         4,
         5,
         7,
         8,9;

DROP TABLE IF EXISTS raena_analytics.total_view_item;


CREATE TABLE raena_analytics.total_view_item AS
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       transaction_date,
       bucket,
       count(distinct profile_objectid) total_event,
        profile_phone
FROM
  (SELECT profile_objectid,
          profile_phone,
          profile_email,
          profile_platform,
          eventname,
          cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
   FROM clevertap.view_item) A
LEFT JOIN raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
left join raena_user_management.user C on A.profile_phone = replace(C.mobile,'+','') 
left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
GROUP BY 1,
         2,
         3,5;



DROP TABLE IF EXISTS raena_analytics.total_checkout;


CREATE TABLE raena_analytics.total_checkout AS
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       transaction_date,
       bucket,
       count(distinct profile_objectid) total_event,
       profile_phone
FROM
  (SELECT profile_objectid,
          profile_phone,
          profile_email,
          profile_platform,
          eventname,
          cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
   FROM clevertap.begin_checkout) A
LEFT JOIN raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
left join raena_user_management.user C on A.profile_phone = replace(C.mobile,'+','') 
left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
GROUP BY 1,
         2,
         3,5;


drop table if exists raena_analytics.order_base_user_transaction;

create table raena_analytics.order_base_user_transaction
as
SELECT reseller_id ,
          replace(reseller_mobile,'+','') profile_phone,
          transaction_date::date transaction_date,
          count(DISTINCT external_id ) total_orders,
          sum(quantity*discounted_price) AS revenue,
          sum((retail_price*quantity)-(seller_margin*quantity)) as Pre_discount_revenue,
          sum(quantity) total_quantity
   FROM raena_analytics.gm_dashboard
   WHERE is_campaign = 'false'
     AND order_placed_by ='App'
   GROUP BY 1,
            2,
            3;


DROP TABLE IF EXISTS raena_analytics.total_first_transacting_user;


CREATE TABLE raena_analytics.total_first_transacting_user AS
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       transaction_date,
       profile_phone,
       bucket,
       count(1) total_event ,
       Count(DISTINCT A.reseller_id) Total_users,
       sum(total_orders) total_orders,
       sum(revenue) revenue,
       sum(Pre_discount_revenue)Pre_discount_revenue,
       sum(total_quantity) total_quantity 
FROM raena_analytics.order_base_user_transaction A
LEFT JOIN raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
left join raena_analytics.cohort_level_reseller on  A.reseller_id  = cohort_level_reseller.reseller_id  
GROUP BY 1,
         2,
         3,4;


drop table if exists raena_analytics.base_matrics_table;

CREATE TABLE raena_analytics.base_matrics_table AS
SELECT A.transaction_date,
       A.user_type,
       A.bucket,
       A.install ,
       B.register,
       avg_dau,
       avg_wau,
       avg_mau,
       pdp_unique,
       add_to_cart,
       checkout,
       transacting_user,
       gmv,
       uninstall
FROM
  (SELECT date_trunc('Month',transaction_date)::date transaction_date ,
          user_type,
          bucket,
          Sum(Total_users)install
   FROM raena_analytics.total_installed
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Month',transaction_date)::date transaction_date,
          user_type,
          bucket,
          Sum(total_users) register
   FROM raena_analytics.total_registered_user
   GROUP BY 1,
            2,
            3)B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
AND A.bucket = B.bucket
LEFT JOIN
  (SELECT date_trunc('Month',transaction_date)::date transaction_date,
          user_type,
          bucket,
          sum(total_user) /count(DISTINCT transaction_date) avg_dau
   FROM
     (SELECT transaction_date ,
             user_type,
             bucket ,
             count(DISTINCT profile_phone) total_user
      FROM raena_analytics.active_user
      GROUP BY 1,
               2,
               3) A
   GROUP BY 1,
            2,
            3) C ON A.transaction_date = C.transaction_date
AND A.user_type = C.user_type
AND A.bucket = C.bucket
LEFT JOIN
  (SELECT date_trunc('Month',transaction_date)::date transaction_date,
          user_type,
          bucket,
          sum(total_user) /count(DISTINCT transaction_date) avg_wau
   FROM
     (SELECT date_trunc('Week',transaction_date)::date transaction_date,
             user_type,
             bucket ,
             count(DISTINCT profile_phone) total_user
      FROM raena_analytics.active_user
      GROUP BY 1,
               2,
               3)
   GROUP BY 1,
            2,
            3) D ON A.transaction_date = D.transaction_date
AND A.user_type = D.user_type
AND A.bucket = D.bucket
LEFT JOIN
  (SELECT date_trunc('Month',transaction_date)::date transaction_date,
          user_type,
          bucket ,
          count(DISTINCT profile_phone) avg_mau
   FROM raena_analytics.active_user
   GROUP BY 1,
            2,
            3) E ON A.transaction_date = E.transaction_date
AND A.user_type = E.user_type
AND A.bucket = E.bucket
LEFT JOIN
  (SELECT date_trunc('Month',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(distinct profile_phone) pdp_unique
   FROM raena_analytics.total_view_item
   GROUP BY 1,
            2,
            3) F ON A.transaction_date = F.transaction_date
AND A.user_type = F.user_type
AND A.bucket = F.bucket
LEFT JOIN
  (SELECT date_trunc('Month',ts)::date transaction_date,
          new_existing_flag user_type,
          bucket,
          count(DISTINCT phone) add_to_cart
   FROM raena_analytics.OM_Events_user_type
   LEFT JOIN raena_user_management.user C ON OM_Events_user_type.phone = replace(C.mobile,'+','')
   LEFT JOIN raena_analytics.cohort_level_reseller ON C.id = cohort_level_reseller.reseller_id
   WHERE events = 'add_to_cart'
   GROUP BY 1,
            2,
            3) G ON A.transaction_date = G.transaction_date
AND A.user_type = G.user_type
AND A.bucket = G.bucket
LEFT JOIN
  (SELECT date_trunc('Month',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(distinct profile_phone) checkout
   FROM raena_analytics.total_checkout
   GROUP BY 1,
            2,
            3) H ON A.transaction_date = H.transaction_date
AND A.user_type = H.user_type
AND A.bucket = H.bucket
LEFT JOIN
  (SELECT date_trunc('Month',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) transacting_user,
          sum(Pre_discount_revenue) gmv
   FROM raena_analytics.total_first_transacting_user
   GROUP BY 1,
            2,
            3) I ON A.transaction_date = I.transaction_date
AND A.user_type = I.user_type
AND A.bucket = I.bucket
LEFT JOIN
  (SELECT date_trunc('Month',transaction_date)::date transaction_date ,
          user_type,
          bucket,
          Sum(Total_users)uninstall
   FROM raena_analytics.total_uninstalled
   GROUP BY 1,
            2,
            3) J ON A.transaction_date = J.transaction_date
AND A.user_type = j.user_type
AND A.bucket = j.bucket;          

DROP TABLE IF EXISTS raena_analytics.post_discount_gm;


CREATE TABLE raena_analytics.post_discount_gm AS
SELECT transaction_date,
       case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',B.first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',B.First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',B.first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',B.First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',B.first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',B.First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       cohort bucket,
       sum(discounted_price*quantity) discounted_price,
       sum(cogs*quantity) cogs,
       count(DISTINCT external_id) AS Number_of_orders
FROM raena_analytics.OM_GM_DB_Product_category A
LEFT JOIN raena_analytics.user_type_table B ON replace(A.reseller_mobile,'+','') =B.reseller_mobile
WHERE is_campaign='false'
  AND order_placed_by='App'
GROUP BY 1,
         2,3;

drop table if exists raena_analytics.Active_user_for_mau_stage;

CREATE TABLE raena_analytics.Active_user_for_mau_stage AS
 SELECT profile_objectid,
          profile_phone,
          profile_email,
          profile_platform,
          cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
   FROM clevertap.app_installed
   union
    SELECT profile_objectid,
          profile_phone,
          profile_email,
          profile_platform,
          cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
   FROM clevertap.app_launched;



DROP TABLE IF EXISTS raena_analytics.Active_user_for_mau;


CREATE TABLE raena_analytics.Active_user_for_mau AS
SELECT date_trunc('Month',A.transaction_date)::date event_created_date,
       date_trunc('Month',first_install_date)::date install_date,
       date_trunc('Month',First_transaction_date)::date first_transaction_date,
       CASE
           WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',(first_install_date))::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date>=date_trunc('Month',first_install_date)::date
                AND (date_trunc('Month',A.transaction_date)::date <= date_trunc('Month',First_transaction_date)::date
                     OR date_trunc('Month',First_transaction_date)::date IS NULL) THEN 'Activation'
           ELSE 'Existing'
       END user_type,
       transaction_date,
       count(1) total_event ,
       A.profile_phone,
       A.profile_objectid
FROM raena_analytics.Active_user_for_mau_stage A
LEFT JOIN
  (SELECT profile_objectid profile_objectid,
          cast(min(transaction_date) AS DATE) First_transaction_date
   FROM
     (SELECT profile_objectid, (cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) transaction_date
      FROM clevertap.charged) A
   GROUP BY 1) B ON A.profile_objectid = B.profile_objectid
LEFT JOIN
  (SELECT profile_objectid,
          MIN(cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) first_install_date
   FROM clevertap.app_installed
   GROUP BY profile_objectid) C ON A.profile_objectid= C.profile_objectid --LEFT JOIN raena_user_management.user D ON A.profile_phone = replace(D.mobile,'+','')
GROUP BY 1,
         2,
         3,
         4,
         5,
         7,
         8;


/*
DROP TABLE IF EXISTS raena_analytics.lm_revenue_gm;


CREATE TABLE raena_analytics.lm_revenue_gm AS
SELECT transaction_date,
       case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       sum(CASE WHEN a.external_id = BB.order_id
           AND a.sku = BB.sku
           AND (CASE WHEN item_type LIKE '%Bundle%' THEN 'Bundle' ELSE 'Product' END)=BB.product_class THEN BB.new_shipment_amount ELSE CC.new_shipment_amount END) shipping_amount,
       sum(a.discounted_price*a.quantity) discounted_price
FROM
  (SELECT *
   FROM raena_analytics.OM_GM_DB_Product_category
   WHERE order_placed_by ='reseller'
     AND is_campaign='false') A
LEFT JOIN raena_analytics.sku_level_shipping_fee_final BB ON a.external_id = BB.order_id
AND a.sku = BB.sku
AND (CASE
         WHEN item_type LIKE '%Bundle%' THEN 'Bundle'
         ELSE 'Product'
     END)=BB.product_class
LEFT JOIN raena_analytics.sku_level_shipping_fee_old_final CC ON a.external_id = CC.external_id
AND a.sku = CC.sku
AND (CASE
         WHEN item_type LIKE '%Bundle%' THEN 'Bundle'
         ELSE 'Product'
     END)=CC.product_class
LEFT JOIN raena_analytics.user_type_table B ON A.reseller_mobile=B.reseller_mobile
GROUP BY 1,
         2;

DROP TABLE IF EXISTS raena_analytics.gm_order_count;


CREATE TABLE raena_analytics.gm_order_count AS
SELECT transaction_date,
       case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       sum(Customer_order) Customer_order,
       sum(Marketplace_order)Marketplace_order,
       sum(total_dropship_order) total_dropship_order,
       sum(total_order) total_order
FROM
  (SELECT transaction_date,
          reseller_mobile,
          count(DISTINCT CASE WHEN order_recipient= 'Customer' THEN external_id END) AS Customer_order,
          count(DISTINCT CASE WHEN order_recipient= 'Marketplace' THEN external_id END) AS Marketplace_order,
          count(DISTINCT CASE WHEN order_recipient IN ('Customer', 'Marketplace') THEN external_id END) total_dropship_order,
          count(DISTINCT external_id) total_order
   FROM OM_GM_DB_Product_category
   WHERE is_campaign='false'
     AND order_placed_by ='reseller'
   GROUP BY 1,
            2) A
LEFT JOIN raena_analytics.user_type_table B ON A.reseller_mobile=B.reseller_mobile
GROUP BY 1,
         2;
*/

DROP TABLE IF EXISTS raena_analytics.post_discount_dsf_revenue;


CREATE TABLE raena_analytics.post_discount_dsf_revenue AS
SELECT transaction_date,
       case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na'
       END user_type,
       sum(payment_amount) discounted_price,
       count(DISTINCT id) AS Number_of_orders,
       count(DISTINCT reseller_id) AS total_users
FROM
 (SELECT reseller_id ,
          external_id id ,
          discounted_price*quantity payment_amount ,
          reseller_mobile,
          transaction_date
   FROM raena_analytics.gm_dashboard
   WHERE is_campaign='false'
     AND lower(external_id) LIKE '%dsf') A
LEFT JOIN raena_analytics.user_type_table B ON A.reseller_mobile =B.reseller_mobile
GROUP BY 1,
         2;

----OM_Events_user_type;created  in db_funnel report 



select max(transaction_date) uninstalled_td from raena_analytics.total_uninstalled;
select max(transaction_date) installed_td from raena_analytics.total_installed;
select max(transaction_date) launched_td from raena_analytics.Active_user;
select max(transaction_date) view_td from raena_analytics.total_view_item;
select max(transaction_date) gm_td from raena_analytics.lm_revenue_gm;



DROP TABLE IF EXISTS raena_analytics.overall_conversion_trend;


CREATE TABLE raena_analytics.overall_conversion_trend AS
SELECT date_trunc('day',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Day'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(total_orders) AS decimal(22,4)) total_orders,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('day',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          sum(total_orders) total_orders
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
AND A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Month',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Month'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(total_orders) AS decimal(22,4)) total_orders,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('Month',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Month',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          sum(total_orders) total_orders
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
AND A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Week',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Week'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(total_orders) AS decimal(22,4)) total_orders,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('Week',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Week',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          sum(total_orders) total_orders
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
AND A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Quarter',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Quarter'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(total_orders) AS decimal(22,4)) total_orders,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('Quarter',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Quarter',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          sum(total_orders) total_orders
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
AND A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Year',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Year'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(total_orders) AS decimal(22,4)) total_orders,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('Year',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Year',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          sum(total_orders) total_orders
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
AND A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,
         5;



DROP TABLE IF EXISTS raena_analytics.average_dau_trend;


CREATE TABLE raena_analytics.average_dau_trend AS
SELECT A.transaction_date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Day'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.active_user 
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Week',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Week'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau 
FROM
  (SELECT date_trunc('Day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.active_user 
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Month',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Month'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau 
FROM
  (SELECT date_trunc('Day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.active_user 
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Quarter',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Quarter'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau 
FROM
  (SELECT date_trunc('Day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.active_user 
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Year',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Year'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau 
FROM
  (SELECT date_trunc('Day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.active_user 
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5;


DROP TABLE IF EXISTS raena_analytics.overall_converting_user_trend;


CREATE TABLE raena_analytics.overall_converting_user_trend AS
SELECT date_trunc('Year',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Year'frequency,
             A.user_type ,
             A.bucket,
             cast(sum(total_users) AS decimal(22,4)) total_user,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
FROM
  (SELECT date_trunc('year',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_objectid) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('year',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          count(DISTINCT profile_phone) total_users
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
and A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Quarter',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Quarter'frequency,
             A.user_type ,
             A.bucket,
             cast(sum(total_users) AS decimal(22,4)) total_user,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
FROM
  (SELECT date_trunc('Quarter',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_objectid) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Quarter',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          count(DISTINCT profile_phone) total_users
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
and A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Month',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Month'frequency,
             A.user_type ,
             A.bucket,
             cast(sum(total_users) AS decimal(22,4)) total_user,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
FROM
  (SELECT date_trunc('Month',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_objectid) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Month',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          count(DISTINCT profile_phone) total_users
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
and A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Week',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Week'frequency,
             A.user_type ,
             A.bucket,
             cast(sum(total_users) AS decimal(22,4)) total_user,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
FROM
  (SELECT date_trunc('Week',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_objectid) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Week',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          count(DISTINCT profile_phone) total_users
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
and A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Day',A.transaction_date)::date AS transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Day'frequency,
             A.user_type ,
             A.bucket,
             cast(sum(total_users) AS decimal(22,4)) total_user,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
FROM
  (SELECT date_trunc('Day',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type,
          raena_analytics.product_metrics_calendar.bucket,
          count(DISTINCT profile_objectid) MAU
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) A
LEFT JOIN
  (SELECT date_trunc('Day',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
          raena_analytics.product_metrics_calendar.user_type ,
          raena_analytics.product_metrics_calendar.bucket ,
          count(DISTINCT profile_phone) total_users
   FROM raena_analytics.product_metrics_calendar
   LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
   AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
   AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
   GROUP BY 1,
            2,
            3) B ON A.transaction_date = B.transaction_date
AND A.user_type = B.user_type
and A.bucket = B.bucket
GROUP BY 1,
         2,
         3,
         4,5;


DROP TABLE IF EXISTS raena_analytics.arpu_trend;


CREATE TABLE raena_analytics.arpu_trend AS
SELECT date_trunc('Day',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_year,
       'Day'frequency,
            user_type ,
            bucket,
            sum(total_orders) total_orders,
            count(distinct profile_phone) transacting_user,
            cast(sum(revenue) AS decimal(22,4)) total_revenue,
            cast(sum(total_quantity) as decimal(22,4)) total_quantity,
            CASE
                WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
            END arpu
FROM raena_analytics.total_first_transacting_user
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Week',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_time,
       'Week'frequency,
             user_type ,
             bucket,
             sum(total_orders) total_orders,
             count(distinct profile_phone) transacting_user,
             cast(sum(revenue) AS decimal(22,4)) total_revenue,
             cast(sum(total_quantity) as decimal(22,4)) total_quantity,
             CASE
                 WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
             END arpu
FROM raena_analytics.total_first_transacting_user
GROUP BY 1,
         2,
         3,
            4,5
UNION ALL
SELECT date_trunc('Month',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_time,
       'Month'frequency,
              user_type ,
              bucket,
              sum(total_orders) total_orders,
              count(distinct profile_phone) transacting_user,
              cast(sum(revenue) AS decimal(22,4)) total_revenue,
              cast(sum(total_quantity) as decimal(22,4)) total_quantity,
              CASE
                  WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
              END arpu
FROM raena_analytics.total_first_transacting_user
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Quarter',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_time,
       'Quarter'frequency,
                user_type ,
                bucket,
                sum(total_orders) total_orders,
                count(distinct profile_phone) transacting_user,
                cast(sum(revenue) AS decimal(22,4)) total_revenue,
                cast(sum(total_quantity) as decimal(22,4)) total_quantity,
                CASE
                    WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
                END arpu
FROM raena_analytics.total_first_transacting_user
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Year',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_time,
       'Year'frequency,
             user_type ,
             bucket,
             sum(total_orders) total_orders,
             count(distinct profile_phone) transacting_user,
             cast(sum(revenue) AS decimal(22,4)) total_revenue,
             cast(sum(total_quantity) as decimal(22,4)) total_quantity,
             CASE
                 WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
             END arpu
FROM raena_analytics.total_first_transacting_user
GROUP BY 1,
         2,
         3,
         4,5 ;




DROP TABLE IF EXISTS raena_analytics.pdp_trend;


CREATE TABLE raena_analytics.pdp_trend AS
SELECT A.transaction_date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Day'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_view_item
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Week',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Week'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_view_item
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Month',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Month'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_view_item
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Quarter',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Quarter'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_view_item
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Year',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Year'frequency,
            A.user_type ,
             A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_view_item
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5;



DROP TABLE IF EXISTS raena_analytics.add_to_cart_trend;


CREATE TABLE raena_analytics.add_to_cart_trend AS
SELECT A.transaction_date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Day'frequency,
            A.user_type ,
            A.bucket,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',ts)::date transaction_date,
          new_existing_flag user_type,
          bucket,
          count(DISTINCT phone) MAU
   from raena_analytics.OM_Events_user_type 
        left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','') 
         left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
   where events = 'add_to_cart'
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Week',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Week'frequency,
            A.user_type ,
            A.bucket,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',ts)::date transaction_date,
          new_existing_flag user_type,
          bucket,
          count(DISTINCT phone) MAU
   from raena_analytics.OM_Events_user_type 
        left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','') 
         left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
         where events = 'add_to_cart'
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Month',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Month'frequency,
            A.user_type ,
            A.bucket,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',ts)::date transaction_date,
          new_existing_flag user_type,
          bucket,
          count(DISTINCT phone) MAU
   from raena_analytics.OM_Events_user_type 
        left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','') 
         left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
         where events = 'add_to_cart'
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Quarter',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Quarter'frequency,
            A.user_type ,
            A.bucket,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',ts)::date transaction_date,
          new_existing_flag user_type,
          bucket,
          count(DISTINCT phone) MAU
   from raena_analytics.OM_Events_user_type 
        left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','') 
         left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
         where events = 'add_to_cart'
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Year',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Year'frequency,
            A.user_type ,
             A.bucket,
             cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',ts)::date transaction_date,
          new_existing_flag user_type,
          bucket,
          count(DISTINCT phone) MAU
   from raena_analytics.OM_Events_user_type 
        left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','') 
         left join raena_analytics.cohort_level_reseller on  C.id  = cohort_level_reseller.reseller_id  
         where events = 'add_to_cart'
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5;



DROP TABLE IF EXISTS raena_analytics.checkout_trend;


CREATE TABLE raena_analytics.checkout_trend AS
SELECT A.transaction_date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Day'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_checkout
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Week',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Week'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_checkout
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Month',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Month'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_checkout
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Quarter',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Quarter'frequency,
            A.user_type ,
            A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_checkout
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5
UNION ALL
SELECT date_trunc('Year',transaction_date)::date transaction_time,
       date_part('Year',A.transaction_date) AS transaction_year,
       'Year'frequency,
            A.user_type ,
             A.bucket,
            cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
FROM
  (SELECT date_trunc('day',transaction_date)::date transaction_date,
          user_type,
          bucket,
          count(DISTINCT profile_phone) MAU
   FROM raena_analytics.total_checkout
   GROUP BY 1,
            2,
            3) A
GROUP BY 1,
         2,
         3,
         4,
         5;   
DROP TABLE IF EXISTS raena_analytics.install_uninstall_ratio_trend;


CREATE TABLE raena_analytics.install_uninstall_ratio_trend AS
SELECT date_trunc('Year',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
       date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_year,
       'Year'frequency,
             raena_analytics.product_metrics_calendar.user_type ,
             raena_analytics.product_metrics_calendar.bucket ,
             cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
             cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
FROM raena_analytics.product_metrics_calendar
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Quarter',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
       date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_time,
       'Quarter'frequency,
                raena_analytics.product_metrics_calendar.user_type ,
               raena_analytics.product_metrics_calendar.bucket ,
             cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
             cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
FROM raena_analytics.product_metrics_calendar
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Month',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
       date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_time,
       'Month'frequency,
              raena_analytics.product_metrics_calendar.user_type ,
               raena_analytics.product_metrics_calendar.bucket ,
             cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
             cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
FROM raena_analytics.product_metrics_calendar
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Week',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
       date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_time,
       'Week'frequency,
             raena_analytics.product_metrics_calendar.user_type ,
              raena_analytics.product_metrics_calendar.bucket ,
             cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
             cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
FROM raena_analytics.product_metrics_calendar
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Day',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
       date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_time,
       'Day'frequency,
            raena_analytics.product_metrics_calendar.user_type ,
             raena_analytics.product_metrics_calendar.bucket ,
             cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
             cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
FROM raena_analytics.product_metrics_calendar
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
GROUP BY 1,
         2,
         3,
         4,5;


DROP TABLE IF EXISTS raena_analytics.mau_trend;


CREATE TABLE raena_analytics.mau_trend AS
SELECT date_trunc('Year',transaction_date)::date AS transaction_date,
       date_part('Year',transaction_date) AS transaction_time,
       'Year'frequency,
             user_type ,
             bucket,
             cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
FROM raena_analytics.active_user
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Quarter',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_time,
       'Quarter'frequency,
                user_type ,
               bucket,
             cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
FROM raena_analytics.active_user
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Month',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_time,
       'Month'frequency,
              user_type ,
              bucket,
             cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
FROM raena_analytics.active_user
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Week',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_time,
       'Week'frequency,
             user_type ,
             bucket,
             cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
FROM raena_analytics.active_user
GROUP BY 1,
         2,
         3,
         4,5
UNION ALL
SELECT date_trunc('Day',transaction_date)::date AS transaction_time,
       date_part('Year',transaction_date) AS transaction_time,
       'Day'frequency,
            user_type ,
            bucket,
             cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
FROM raena_analytics.active_user
GROUP BY 1,
         2,
         3,
         4,5;


" > /home/ec2-user/cronjob/redshift/sql_code/product_matrics_etl.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/product_matrics_etl.sql