#!/bin/bash

echo "
                   
--------------------------Retention Data --------------------------
drop table if exists OM_Reseller_Wise_latest_tier;

 create table OM_Reseller_Wise_latest_tier
 as
 select mobile,tier from(
 select mobile,tier,row_number() over (partition by mobile order by created_date desc) as R
 from (
 select distinct replace(json_extract_path_text(o.reseller_info,'mobile',TRUE),'+','') as mobile,
 json_extract_path_text(o.reseller_info,'tierName',TRUE) as tier,
 cast(created_at as date) as created_date
 from raena_order_management.order o 
 )
 ) where R=1 and mobile notnull;

Drop table if exists Retention_new_filters_install;


create table Retention_new_filters_install
  as
  select distinct TO_CHAR(o.created_at, 'Month') as month,cast(date_part('year',o.created_at) as varchar) as year,
  case when shipping_to='Reseller' then 'Reseller' else 'Dropshipper' end as customer_type,
  order_placed_by,
  case when order_placed_by='admin' then 'admin' else marketplace end as Sales_channel,provider,
  c.channel_name as channel_name,
  replace(json_extract_path_text(o.reseller_info,'mobile',TRUE),'+','') mobile,
  verification_status,tier
  FROM raena_user_management."user" u
  left join raena_order_management.order o
  on replace(json_extract_path_text(o.reseller_info,'mobile',TRUE),'+','')=replace(u.mobile,'+','')
  left join OM_channel c on cast(c.id as text)=cast(o.channel_id as text)
  left join (
  select mobile,tier from(
  select mobile,tier,row_number() over (partition by mobile order by created_date desc) as R
  from (
  select distinct replace(json_extract_path_text(o.reseller_info,'mobile',TRUE),'+','') as mobile,
  json_extract_path_text(o.reseller_info,'tierName',TRUE) as tier,
  cast(created_at as date) as created_date
  from raena_order_management.order o 
  )
  ) where R=1 and mobile notnull
 ) t on u.mobile=t.mobile;


Drop table if exists public.DAU_RETENTION;



create table DAU_RETENTION
as
select *
from 
(
select distinct base_1.mobile,cast(datepart(year,base_1.created_date) as varchar) as conversion_year,
TO_CHAR(base_1.created_date, 'Month') as conversion_month,
datepart(month,base_1.created_date) as month_sort,base_1.created_date,base_1.bucket,
case when datediff(month,base_1.created_date,base_2.created_date)=0 then base_1.mobile end as M0,
case when datediff(month,base_1.created_date,base_3.created_date)=1 then base_1.mobile end as M1,
case when datediff(month,base_1.created_date,base_3.created_date)=2 then base_1.mobile end as M2,
case when datediff(month,base_1.created_date,base_3.created_date)=3 then base_1.mobile end as M3,
case when datediff(month,base_1.created_date,base_3.created_date)=4 then base_1.mobile end as M4,
case when datediff(month,base_1.created_date,base_3.created_date)=5 then base_1.mobile end as M5,
case when datediff(month,base_1.created_date,base_3.created_date)=6 then base_1.mobile end as M6,
case when datediff(month,base_1.created_date,base_3.created_date)>6 then base_1.mobile end as GT_M6
from
(
select *,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket from 
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,payment_amount,
row_number() over(partition by replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','')
order by cast(created_at AS Date)) created_rnk
FROM raena_order_management.order
WHERE payment_status = 'Paid' 
) a where a.created_rnk=1
) base_1
inner join 
(
select mobile,date_trunc('month',created_date) as created_date,sum(payment_amount) as payment_amount
from
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,payment_amount,
row_number() over(partition by replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+',''),
date_part(year,cast(created_at AS Date)),date_part(month,cast(created_at AS Date))
order by date_part(year,cast(created_at AS Date)),date_part(month,cast(created_at AS Date))) created_rnk
FROM raena_order_management.order
WHERE payment_status = 'Paid'  
) 
group by 1,2
) base_2 on base_1.mobile=base_2.mobile
left join 
(
select distinct date_trunc('month',created_date) as created_date,active_user
from
(
SELECT DISTINCT cast(to_date(substring(ts,1,8),'yyyymmdd') AS date) created_date,
                   case when profile_phone <> '' then profile_phone 
                   else profile_objectid end Active_user,
                   dense_rank() over(partition by (case when profile_phone <> '' then profile_phone 
                   else profile_objectid end) order by date_part('year',cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)),date_part('month',cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)) ) created_rnk_2
                    FROM analytics.clevertap.app_launched al
   WHERE eventname= 'App Launched' and profile_phone <> ''
   )

) base_3 on base_1.mobile=base_3.active_user
) bucket_final;

Drop table if exists public.INSTALLBASE_REVENUE_RETENTION;



create table public.INSTALLBASE_REVENUE_RETENTION
as
select *
from
( 
select cast(acquisition_year as varchar) as acquisition_year,acquisition_month,month_sort,bucket_check.bucket,install_base.Active_user,
case when datediff(month,install_base.created_date,conversion_base.created_date)=0 then conversion_base.payment_amount end as M0,
case when datediff(month,install_base.created_date,conversion_base.created_date)=1 then conversion_base.payment_amount end as M1,
case when datediff(month,install_base.created_date,conversion_base.created_date)=2 then conversion_base.payment_amount end as M2,
case when datediff(month,install_base.created_date,conversion_base.created_date)=3 then conversion_base.payment_amount end as M3,
case when datediff(month,install_base.created_date,conversion_base.created_date)=4 then conversion_base.payment_amount end as M4,
case when datediff(month,install_base.created_date,conversion_base.created_date)=5 then conversion_base.payment_amount end as M5,
case when datediff(month,install_base.created_date,conversion_base.created_date)=6 then conversion_base.payment_amount end as M6,
case when datediff(month,install_base.created_date,conversion_base.created_date)>6 then conversion_base.payment_amount end as GT_M6
from
(
select distinct base.Active_user,datepart(year,base.created_date) as acquisition_year,
TO_CHAR(base.created_date, 'Month') as acquisition_month,
datepart(month,base.created_date) as month_sort,base.created_date
from
(
SELECT DISTINCT cast(to_date(substring(ts,1,8),'yyyymmdd') AS date) as created_date,
				date_part('month',cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)) month_sort,
                 case when profile_phone <> '' then profile_phone 
                   else profile_objectid end Active_user,
                   dense_rank() over(partition by (case when profile_phone <> '' then profile_phone 
                   else profile_objectid end) order by cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)) created_rnk
                    FROM analytics.clevertap.app_launched al
WHERE eventname= 'App Launched'  --and profile_phone in ('628814617694')
) base where created_rnk=1
) install_base
inner join 
(
select mobile,created_date,payment_amount from
(
select mobile,created_date,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket,payment_amount,row_number() over (partition by mobile order by created_date) as rnk
from 
(
select distinct mobile, date_trunc('month',created_date) as created_date,sum(payment_amount) as payment_amount
from
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,
payment_amount
FROM raena_order_management.order
WHERE payment_status = 'Paid' 
) group by 1,2
) 
) 
) conversion_base on install_base.active_user=conversion_base.mobile
inner join 
(
select mobile,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket from
(
SELECT replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
sum(payment_amount) as payment_amount
FROM raena_order_management.order
WHERE payment_status = 'Paid'
group by 1
)
) bucket_check on install_base.active_user=bucket_check.mobile
) a;

Drop table if exists public.INSTALLBASE_RESELLER_RETENTION;


create table public.INSTALLBASE_RESELLER_RETENTION
as
select *
from 
( 
select cast(acquisition_year as varchar) as acquisition_year,acquisition_month,month_sort,bucket_check.bucket,install_base.Active_user,
case when datediff(month,install_base.created_date,conversion_base.created_date)=0 then install_base.Active_user end as M0,
case when datediff(month,install_base.created_date,conversion_base.created_date)=1 then install_base.Active_user end as M1,
case when datediff(month,install_base.created_date,conversion_base.created_date)=2 then install_base.Active_user end as M2,
case when datediff(month,install_base.created_date,conversion_base.created_date)=3 then install_base.Active_user end as M3,
case when datediff(month,install_base.created_date,conversion_base.created_date)=4 then install_base.Active_user end as M4,
case when datediff(month,install_base.created_date,conversion_base.created_date)=5 then install_base.Active_user end as M5,
case when datediff(month,install_base.created_date,conversion_base.created_date)=6 then install_base.Active_user end as M6,
case when datediff(month,install_base.created_date,conversion_base.created_date)>6 then install_base.Active_user end as GT_M6
from
(
select distinct base.Active_user,datepart(year,base.created_date) as acquisition_year,
TO_CHAR(base.created_date, 'Month') as acquisition_month,
datepart(month,base.created_date) as month_sort,base.created_date
from
(
SELECT DISTINCT cast(to_date(substring(ts,1,8),'yyyymmdd') AS date) as created_date,
				date_part('month',cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)) month_sort,
                 case when profile_phone <> '' then profile_phone 
                   else profile_objectid end Active_user,
                   dense_rank() over(partition by (case when profile_phone <> '' then profile_phone 
                   else profile_objectid end) order by cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)) created_rnk
                    FROM analytics.clevertap.app_launched al
WHERE eventname= 'App Launched'  --and profile_phone in ('628814617694')
) base where created_rnk=1
) install_base
inner join 
(
select mobile,created_date from
(
select mobile,created_date,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket,row_number() over (partition by mobile order by created_date) as rnk
from 
(
select distinct mobile, date_trunc('month',created_date) as created_date,sum(payment_amount) as payment_amount
from
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,
payment_amount
FROM raena_order_management.order
WHERE payment_status = 'Paid' 
) group by 1,2
) 
) 
) conversion_base on install_base.active_user=conversion_base.mobile
inner join 
(
select mobile,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket from
(
SELECT replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
sum(payment_amount) as payment_amount
FROM raena_order_management.order
WHERE payment_status = 'Paid'
group by 1
)
) bucket_check on install_base.active_user=bucket_check.mobile
) a;

Drop table if exists public.DAU_RETENTION_BASIS_INSTALL_COHORT;


create table public.DAU_RETENTION_BASIS_INSTALL_COHORT
as
select *
from
(
select install_base.*,bucket.bucket
from
(
select distinct a.Active_user,datepart(year,a.created_date) as acquisition_year,
TO_CHAR(a.created_date, 'Month') as acquisition_month,
datepart(month,a.created_date) as month_sort,a.created_date,
case when datediff(month,a.created_date,b.created_date)=0 then a.Active_user end as M0,
case when datediff(month,a.created_date,b.created_date)=1 then a.Active_user end as M1,
case when datediff(month,a.created_date,b.created_date)=2 then a.Active_user end as M2,
case when datediff(month,a.created_date,b.created_date)=3 then a.Active_user end as M3,
case when datediff(month,a.created_date,b.created_date)=4 then a.Active_user end as M4,
case when datediff(month,a.created_date,b.created_date)=5 then a.Active_user end as M5,
case when datediff(month,a.created_date,b.created_date)=6 then a.Active_user end as M6,
case when datediff(month,a.created_date,b.created_date)>6 then a.Active_user end as GT_M6
from
(
select * from 
(
SELECT DISTINCT cast(to_date(substring(ts,1,8),'yyyymmdd') AS date) as created_date,
				date_part('month',cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)) month_sort,
                 case when profile_phone <> '' then profile_phone 
                   else profile_objectid end Active_user,
                   dense_rank() over(partition by (case when profile_phone <> '' then profile_phone 
                   else profile_objectid end) order by cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)) created_rnk
                    FROM analytics.clevertap.app_launched al
WHERE eventname= 'App Launched'  --and profile_phone in ('628814617694')
  ) base where created_rnk=1 ) a 
   inner join 
   (
   SELECT DISTINCT cast(to_date(substring(ts,1,8),'yyyymmdd') AS date) created_date,
                   case when profile_phone <> '' then profile_phone 
                   else profile_objectid end Active_user,
                   dense_rank() over(partition by (case when profile_phone <> '' then profile_phone 
                   else profile_objectid end) order by date_part('year',cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)),date_part('month',cast(to_date(substring(ts,1,8),'yyyymmdd') AS date)) ) created_rnk_2
                    FROM analytics.clevertap.app_launched al
   WHERE eventname= 'App Launched' --and profile_phone in ('628814617694')
   ) b on a.Active_user=b.Active_user
   ) install_base
   left join 
(
 select conversion_base.mobile,date_part(year,created_date) as acquistion_year,TO_CHAR(created_date,'month') as acquistion_month,
bucket_check.bucket
from
(
select mobile,created_date
from
(
select mobile,created_date,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket,row_number() over (partition by mobile order by created_date) as rnk
from 
(
select distinct mobile, date_trunc('month',created_date) as created_date,sum(payment_amount) as payment_amount
from
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,
payment_amount
FROM raena_order_management.order
WHERE payment_status = 'Paid' 
) b group by 1,2
) c
) a where a.rnk=1
)conversion_base 
inner join 
(
select mobile,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket from
(
SELECT replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
sum(payment_amount) as payment_amount
FROM raena_order_management.order
WHERE payment_status = 'Paid'
group by 1
)
) bucket_check on conversion_base.mobile=bucket_check.mobile
) bucket on install_base.active_user=bucket.mobile and date_part(year,install_base.created_date)=bucket.acquistion_year and TO_CHAR(install_base.created_date,'month')=bucket.acquistion_month
  ) bucket_final;

drop table if exists public.OMREVENUE_RETENTION;


create table public.OMREVENUE_RETENTION
as
select * from 
(
select distinct base_1.mobile,cast(datepart(year,base_1.created_date) as varchar) as acquisition_year,
TO_CHAR(base_1.created_date, 'Month') as acquisition_month,
datepart(month,base_1.created_date) as month_sort,base_1.created_date,base_1.bucket,
case when datediff(month,base_1.created_date,base_2.created_date)=0 then base_2.payment_amount end as M0,
case when datediff(month,base_1.created_date,base_2.created_date)=1 then base_2.payment_amount end as M1,
case when datediff(month,base_1.created_date,base_2.created_date)=2 then base_2.payment_amount end as M2,
case when datediff(month,base_1.created_date,base_2.created_date)=3 then base_2.payment_amount end as M3,
case when datediff(month,base_1.created_date,base_2.created_date)=4 then base_2.payment_amount end as M4,
case when datediff(month,base_1.created_date,base_2.created_date)=5 then base_2.payment_amount end as M5,
case when datediff(month,base_1.created_date,base_2.created_date)=6 then base_2.payment_amount end as M6,
case when datediff(month,base_1.created_date,base_2.created_date)>6 then base_2.payment_amount end as GT_M6
from
(
select *,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket from 
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,payment_amount,
row_number() over(partition by replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','')
order by cast(created_at AS Date)) created_rnk
FROM raena_order_management.order
WHERE payment_status = 'Paid' 
) a where a.created_rnk=1
) base_1
inner join 
(
select mobile,date_trunc('month',created_date) as created_date,sum(payment_amount) as payment_amount
from
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,payment_amount,
row_number() over(partition by replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+',''),
date_part(year,cast(created_at AS Date)),date_part(month,cast(created_at AS Date))
order by date_part(year,cast(created_at AS Date)),date_part(month,cast(created_at AS Date))) created_rnk
FROM raena_order_management.order
WHERE payment_status = 'Paid'  
) 
group by 1,2
) base_2 on base_1.mobile=base_2.mobile
) bucket_final;

Drop table if exists public.RESELLER_RETENTION;


create table public.RESELLER_RETENTION
as
select * from 
(
select distinct base_1.mobile,cast(datepart(year,base_1.created_date) as varchar) as acquisition_year,
TO_CHAR(base_1.created_date, 'Month') as acquisition_month,
datepart(month,base_1.created_date) as month_sort,base_1.created_date,base_1.bucket,
case when datediff(month,base_1.created_date,base_2.created_date)=0 then base_1.mobile end as M0,
case when datediff(month,base_1.created_date,base_2.created_date)=1 then base_1.mobile end as M1,
case when datediff(month,base_1.created_date,base_2.created_date)=2 then base_1.mobile end as M2,
case when datediff(month,base_1.created_date,base_2.created_date)=3 then base_1.mobile end as M3,
case when datediff(month,base_1.created_date,base_2.created_date)=4 then base_1.mobile end as M4,
case when datediff(month,base_1.created_date,base_2.created_date)=5 then base_1.mobile end as M5,
case when datediff(month,base_1.created_date,base_2.created_date)=6 then base_1.mobile end as M6,
case when datediff(month,base_1.created_date,base_2.created_date)>6 then base_1.mobile end as GT_M6
from
(
select *,CASE
           WHEN payment_amount < 2000000 THEN '<2M'
           WHEN payment_amount BETWEEN 2000001 AND 10000000 THEN '2-10M'
           WHEN payment_amount>10000000 THEN '+10M'
       END AS Bucket from 
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,payment_amount,
row_number() over(partition by replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','')
order by cast(created_at AS Date)) created_rnk
FROM raena_order_management.order
WHERE payment_status = 'Paid' 
) a where a.created_rnk=1
) base_1
inner join 
(
select mobile,date_trunc('month',created_date) as created_date,sum(payment_amount) as payment_amount
from
(
SELECT distinct replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+','') as mobile,
cast(created_at AS Date) created_date,payment_amount,
row_number() over(partition by replace(json_extract_path_text(raena_order_management.order.reseller_info,'mobile',TRUE),'+',''),
date_part(year,cast(created_at AS Date)),date_part(month,cast(created_at AS Date))
order by date_part(year,cast(created_at AS Date)),date_part(month,cast(created_at AS Date))) created_rnk
FROM raena_order_management.order
WHERE payment_status = 'Paid'  
) 
group by 1,2
) base_2 on base_1.mobile=base_2.mobile
) bucket_final;                  
                   
                  
                  
                 
                  
                  
                  
                   
                  
                 
             
                  
                 
         " > /home/ec2-user/cronjob/warehouseAnalysis/retention.sql

psql "host=raen-prd-sg-redshift-cluster.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f/home/ec2-user/cronjob/warehouseAnalysis/retention.sql > retention.log