36 lines
1.7 KiB
Bash
36 lines
1.7 KiB
Bash
|
#!/bin/bash
|
||
|
|
|
||
|
|
echo "
|
||
|
|
|
||
|
|
drop table om_New_Existing_flag
|
||
|
|
create table om_New_Existing_flag
|
||
|
|
as
|
||
|
|
select a.*,b.tier
|
||
|
|
from
|
||
|
|
select distinct cast(App_install_date as date) as App_install_date,profile_phone,profile_email,case when R_flag=1 then 'New' else 'Existing' end as flag
|
||
|
|
from
|
||
|
|
(
|
||
|
|
select date_trunc('month', App_install_date) - interval '0 month' as App_install_date,profile_phone,profile_email,
|
||
|
|
row_number() over (partition by profile_phone,date_trunc('month', App_install_date) - interval '0 month' order by date_trunc('month', App_install_date) - interval '0 month') as R,
|
||
|
|
row_number() over (partition by profile_phone order by date_trunc('month', App_install_date) - interval '0 month') as R_flag
|
||
|
|
from
|
||
|
|
(
|
||
|
|
select cast(left(ts,4) || '-' || right(left(ts,6),2) || '-' || right(left(ts,8),2) as date) as App_install_date,profile_phone,profile_email
|
||
|
|
from clevertap.app_installed
|
||
|
|
) where profile_phone!=' '
|
||
|
|
) where R=1 --and profile_phone in ('6289876543210','6285320252449')
|
||
|
|
) a
|
||
|
|
left join
|
||
|
|
(
|
||
|
|
select mobile,tier
|
||
|
|
from (
|
||
|
|
select distinct replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') as mobile,
|
||
|
|
json_extract_path_text(reseller_info,'tierName',TRUE) as tier,
|
||
|
|
row_number() over (partition by replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') order by created_at desc) as R_desc
|
||
|
|
from raena_order_management.order
|
||
|
|
) where R_desc=1 and mobile notnull
|
||
|
|
) b on a.profile_phone=b.mobile
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/warehouseAnalysis/OM_New_VS_Existing.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/warehouseAnalysis.sql > OM_New_VS_Existing.log
|