475 lines
22 KiB
Bash
475 lines
22 KiB
Bash
|
#!/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
|