74 lines
5.1 KiB
Bash
74 lines
5.1 KiB
Bash
|
#!/bin/bash
|
||
|
|
|
||
|
|
echo "
|
||
|
|
create table om_app_install_uninstall_rate_reseller_business_metrics_db
|
||
|
|
as
|
||
|
|
select frequency,case when frequency='month' then cast(date_part('year',App_uninstall_date) as varchar)
|
||
|
|
when frequency='quarter' then cast(date_part('year',App_uninstall_date) as varchar)
|
||
|
|
when frequency='day' then concat(left(TO_CHAR(App_uninstall_date,'Month'),3),right(cast(date_part('year',App_uninstall_date) as varchar),2))
|
||
|
|
when frequency='week' then concat(left(TO_CHAR(App_uninstall_date,'Month'),3),right(cast(date_part('year',App_uninstall_date) as varchar),2))
|
||
|
|
end as upper ,
|
||
|
|
case when frequency='month' then cast(date_part('year',App_uninstall_date) as varchar)
|
||
|
|
when frequency='quarter' then cast(date_part('year',App_uninstall_date) as varchar)
|
||
|
|
when frequency='day' then concat(right(cast(date_part('year',App_uninstall_date) as varchar),2),right(cast(date_part('month',App_uninstall_date) as varchar),1))
|
||
|
|
when frequency='week' then concat(right(cast(date_part('year',App_uninstall_date) as varchar),2),right(cast(date_part('month',App_uninstall_date) as varchar),1))
|
||
|
|
end as upper_sort,
|
||
|
|
"time",sort,flag,tier,sum(uninstall_user) as uninstall_user,sum(install_user) as install_user
|
||
|
|
--cast(sum(uninstall_user) as float)/cast(sum(install_user) as float) as UnInstall_rate
|
||
|
|
from
|
||
|
|
(
|
||
|
|
select 'year' as frequency,
|
||
|
|
cast(date_part('year',App_uninstall_date) as varchar) as time,cast(date_part('year',App_uninstall_date) as int) as sort,
|
||
|
|
App_uninstall_date,om_app_uninstalled_reseller_business_metrics_db.flag,om_app_uninstalled_reseller_business_metrics_db.tier,count(distinct public.om_app_uninstalled_reseller_business_metrics_db.user_id) as uninstall_user,
|
||
|
|
count(distinct public.om_app_installed_reseller_business_metrics_db.user_id) as install_user
|
||
|
|
from public.om_app_uninstalled_reseller_business_metrics_db
|
||
|
|
left join public.om_app_installed_reseller_business_metrics_db
|
||
|
|
on app_install_date between cast(dateadd(day,-30,App_uninstall_date) as date) and App_uninstall_date
|
||
|
|
group by 2,3,4,5,6
|
||
|
|
union
|
||
|
|
select 'quarter' as frequency,
|
||
|
|
To_char(App_uninstall_date,'quarter') as time,cast(concat(date_part('year',App_uninstall_date),date_part('quarter',App_uninstall_date)) as int) as sort,
|
||
|
|
App_uninstall_date,om_app_uninstalled_reseller_business_metrics_db.flag,om_app_uninstalled_reseller_business_metrics_db.tier,count(distinct public.om_app_uninstalled_reseller_business_metrics_db.user_id) as uninstall_user,
|
||
|
|
count(distinct public.om_app_installed_reseller_business_metrics_db.user_id) as install_user
|
||
|
|
from public.om_app_uninstalled_reseller_business_metrics_db
|
||
|
|
left join public.om_app_installed_reseller_business_metrics_db
|
||
|
|
on app_install_date between cast(dateadd(day,-30,App_uninstall_date) as date) and App_uninstall_date
|
||
|
|
group by 2,3,4,5,6
|
||
|
|
union
|
||
|
|
select 'month' as frequency,
|
||
|
|
To_char(App_uninstall_date,'month') as time,
|
||
|
|
date_part('month',App_uninstall_date) as sort,
|
||
|
|
App_uninstall_date,om_app_uninstalled_reseller_business_metrics_db.flag,om_app_uninstalled_reseller_business_metrics_db.tier,count(distinct public.om_app_uninstalled_reseller_business_metrics_db.user_id) as uninstall_user,
|
||
|
|
count(distinct public.om_app_installed_reseller_business_metrics_db.user_id) as install_user
|
||
|
|
from public.om_app_uninstalled_reseller_business_metrics_db
|
||
|
|
left join public.om_app_installed_reseller_business_metrics_db
|
||
|
|
on app_install_date between cast(dateadd(day,-30,App_uninstall_date) as date) and App_uninstall_date
|
||
|
|
group by 2,3,4,5,6
|
||
|
|
union
|
||
|
|
select 'week' as frequency,
|
||
|
|
To_char(App_uninstall_date,'week') as month_name,
|
||
|
|
cast(left(To_char(App_uninstall_date,'week'),1) as int) as sort,
|
||
|
|
App_uninstall_date,om_app_uninstalled_reseller_business_metrics_db.flag,om_app_uninstalled_reseller_business_metrics_db.tier,count(distinct public.om_app_uninstalled_reseller_business_metrics_db.user_id) as uninstall_user,
|
||
|
|
count(distinct public.om_app_installed_reseller_business_metrics_db.user_id) as install_user
|
||
|
|
from public.om_app_uninstalled_reseller_business_metrics_db
|
||
|
|
left join public.om_app_installed_reseller_business_metrics_db
|
||
|
|
on app_install_date between cast(dateadd(day,-30,App_uninstall_date) as date) and App_uninstall_date
|
||
|
|
group by 2,3,4,5,6
|
||
|
|
union
|
||
|
|
select 'day' as frequency,
|
||
|
|
cast(date_part('day',App_uninstall_date) as varchar) as month_name,
|
||
|
|
cast(date_part('day',App_uninstall_date) as int) as sort,
|
||
|
|
App_uninstall_date,om_app_uninstalled_reseller_business_metrics_db.flag,om_app_uninstalled_reseller_business_metrics_db.tier,count(distinct public.om_app_uninstalled_reseller_business_metrics_db.user_id) as uninstall_user,
|
||
|
|
count(distinct public.om_app_installed_reseller_business_metrics_db.user_id) as install_user
|
||
|
|
from public.om_app_uninstalled_reseller_business_metrics_db
|
||
|
|
left join public.om_app_installed_reseller_business_metrics_db
|
||
|
|
on app_install_date between cast(dateadd(day,-30,App_uninstall_date) as date) and App_uninstall_date
|
||
|
|
group by 2,3,4,5,6
|
||
|
|
)
|
||
|
|
group by 1,2,3,4,5,6,7
|
||
|
|
order by sort,upper_sort ;
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/warehouseAnalysis/install_uninstall_rate.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/install_uninstall_rate.sql
|