--------------------------DB Funnel New Users--------------------------

Drop table if exists om_clevertap_install_jan_may
create table om_clevertap_install_jan_may
as
select App_install_date,email,phone,user_id
from (
select *,row_number() over (partition by user_id order by app_install_date) as R
from 
(
SELECT '2022-04-01' as App_install_date,email,phone,email as user_id FROM public.clevertap_april_csv
union
SELECT '2022-03-30' as App_install_date,email,phone,email as user_id FROM public.clevertap_march_csv where phone not in (select distinct profile_phone from clevertap.app_installed)
union
select cast(left(ts,4) || '-' || right(left(ts,6),2)  || '-' || right(left(ts,8),2) as date) as App_install_date,profile_email,profile_phone,
case when profile_email=' ' then profile_objectid else profile_email end as user_id
from clevertap.app_installed  
)
) where R=1 

Drop table if exists om_clevertap_install_jan_may_2
create table om_clevertap_install_jan_may_2
as
select user_id,email,phone,to_char(app_install_date,'month') as month,date_part(day,app_install_date) as day,address_line1,address_line2,city,province,app_install_date
from (
select a.user_id,a.email,phone,case when app_install_date='2022-03-30' or app_install_date='2022-04-01' then cast(created_at as date) else app_install_date end as app_install_date,
b.address_line1,address_line2,city,province
from om_clevertap_install_jan_may a
left join (
select mobile,email,created_at,address_line1,address_line2,city,province
from (
select email,replace(mobile,'+','') as mobile,created_at,address_line1,address_line2,city,province,
row_number() over (partition by email order by created_at) as R
from raena_user_management.user 
) 
where R=1 and email notnull) b on a.email=b.email
)

Drop table if exists om_clevertap_install_jan_may_3
create table om_clevertap_install_jan_may_3
as
select a.*,cast(b.created_at as date) as transaction_date,transacted_email
--case when cast(b.created_at as date) isnull then 'Never transacted'
--when cast(b.created_at as date)>=app_install_date and cast(b.created_at as date)<=cast(dateadd(day,30,app_install_date) as date) then 'Yes' else 'No' end as transacted_flag
from om_clevertap_install_jan_may_2 a
left join (select json_extract_path_text(reseller_info,'email',TRUE) as transacted_email ,min(created_at) as created_at
		 from raena_order_management.order where payment_status='Paid' and cast(is_archived as varchar)='false' group by 1 ) b 
on a.email=transacted_email
   
Drop table if exists om_clevertap_install_jan_may_final
create table om_clevertap_install_jan_may_final
as
select *,case when email notnull and rtrim(ltrim(email)) != '' then 'Yes' else 'No' end as email_flag,
case when phone notnull and rtrim(ltrim(phone)) != '' then 'Yes' else 'No' end as phone_flag,
case when address_line1 notnull and rtrim(ltrim(address_line1)) != '' then 'Yes' else 'No' end as address_flag,
case when transacted_email notnull and rtrim(ltrim(transacted_email)) != '' and transaction_date>=app_install_date and transaction_date<=cast(dateadd(day,30,app_install_date) as date) 
then 'Yes' else 'No' end as transacted_flag
from om_clevertap_install_jan_may_3 

--order data
Drop table if exists om_clevertap_install_jan_may_order
create table om_clevertap_install_jan_may_order
as
select * from om_clevertap_install_jan_may_final where transacted_email notnull and rtrim(ltrim(transacted_email)) != ''

--launch
Drop table if exists public.om_app_launched_db_funnel
create table public.om_app_launched_db_funnel
as 
select * from (
select distinct b.app_install_date,a.App_launch_date,a.user_id,
case when b.phone isnull then 'New' 
				when App_launch_date >= app_install_date and App_launch_date < cast(dateadd(day,30,app_install_date) as date) then 'Yes' else 'No' end as flag,
				row_number() over (partition by a.User_id  order by App_launch_date) as R
from (
select distinct cast(left(ts,4) || '-' || right(left(ts,6),2)  || '-' || right(left(ts,8),2) as date) as App_launch_date,
profile_phone,profile_objectid  as launch_profile_objectid ,case when profile_email=' ' then profile_objectid else profile_email end as user_id
				from clevertap.app_launched 
) a 
inner join om_clevertap_install_jan_may_final b on a.user_id=b.user_id
) where flag='Yes' order by user_id