#!/bin/bash

echo "

--------------------------Retention Data --------------------------
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/Retention/Retention_Metabase.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/Retention/Retention_Metabase.sql > etlRetention_Metabase.log