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 ;