SQLize Online / PHPize Online  /  SQLtest Online

drop function if exists fn_load_fcrm_dm_fcscore_stat_tm_abt_alerts_agg(text, timestamp); CREATE or replace FUNCTION fn_load_fcrm_dm_fcscore_stat_tm_abt_alerts_agg(p_customerid text, p_scoring_dt timestamp) RETURNS table ( companyid text , partnerbank text , amount_num numeric , scoring_dt timestamp , legalform text , finom_lt_days_cnt integer , countrycode text , city text , business_type text , rules text , rules_norm text , has_fp1 integer , has_fp2 integer , has_fp3 integer , has_fp4 integer , has_fp5 integer , has_fp7 integer , has_fp9 integer , has_fp integer , has_rai integer , tp_cnt integer , fp_cnt integer , opened_alerts_90d_cnt bigint , opened_last_alerts_90d_cnt bigint , last_alert_name text ) AS $$ begin drop table if exists tt_alerts_agg; create temp table tt_alerts_agg as with tt_transactions as ( select t.customerid as companyid , max(t.partnerbank) as partnerbank , p_scoring_dt as scoring_dt , sum(case when t.amountineuro_num = 0 or t.amountineuro_num is null then t.amount_num else t.amountineuro_num end) as amount_num from public.transactions t where t.customerid = p_customerid and upper(t.status) <> 'DECLINED' and upper(t.status) <> 'REVERSED' and upper(t.type) not like '%FEE%' and upper(t.type) not like '%CASHBACK%' and upper(t.type) not like '%REFUND%' and upper(t.type) not like '%SYSTEM%' and upper(t.type) not like '%CURRENCY%' and upper(t.type) not like '%TOPUP%' and createdtime_dt::date >= p_scoring_dt - interval '90 days' and createdtime_dt::date < p_scoring_dt group by t.customerid , p_scoring_dt ), companies_to_score as ( select sample.companyid , sample.partnerbank as partnerbank , sample.amount_num , sample.scoring_dt as scoring_dt , c.legalform , c.countrycode , c.city , c.businesstype , DATE_PART('day', sample.scoring_dt - c.createdat_dt)::integer as finom_lt_days_cnt from tt_transactions as sample inner join public.companies c on c.id = sample.companyid ), tt_alerts as( select t.companyid , t.risedon_dt , t.resolvedon_dt , t.status , t.rules , t.rules_norm from ( select a.companyid , a.risedon_dt , a.resolvedon_dt , a.status , a.rules , case when a.rules like '%TM%' then trim('-' from substr(a.rules, 0,6)) when a.rules like '%FRAUD%' then trim('-' from substr(a.rules, 0,8)) else a.rules end as rules_norm from public.alerts a where a.risedon_dt::date <= p_scoring_dt and a.risedon_dt >= p_scoring_dt - interval '90 days' )t where t.rules_norm in ('FCRM FC-100 TEST' , 'FCRM FC-11 TEST' , 'FCRM FC-1 PROD' , 'FCRM FP-1 PROD' , 'FCRM FP-3 PROD' , 'ML-1' , 'RAI-1' , 'RAI-10' , 'RAI-14' , 'RAI-23' , 'RAI-24' , 'RAI-25' , 'RAI-27' , 'RAI-4' , 'RAI-7' , 'RAI-8' , 'RAI-OTHER' , 'SNC-6' , 'TM-1' , 'TM-10' , 'TM-5' , 'TM-6' , 'TM-7') ), alerts_for_sample as ( select distinct t.companyid , t.partnerbank , t.amount_num , t.scoring_dt , t.finom_lt_days_cnt , t.legalform , t.countrycode , t.city , t.businesstype , t.status , t.rules , t.rules_norm , t.opened_alerts_90d_cnt , sum(case when t.risedon_dt > t.last_closed_alert_dt and (t.status not in ('Good', 'Bad', 'TruePositive', 'FalsePositive', 'Archived') or t.resolvedon_dt > t.scoring_dt) then 1 else 0 end) over(partition by t.companyid, t.scoring_dt) as opened_last_alerts_90d_cnt , t.last_alert_name from ( select c.companyid , c.partnerbank , c.amount_num , c.scoring_dt , c.finom_lt_days_cnt , c.legalform , c.countrycode , c.city , c.businesstype , a.status , a.rules , a.rules_norm , sum(case when a.status not in ('Good', 'Bad', 'TruePositive', 'FalsePositive', 'Archived') or a.resolvedon_dt > c.scoring_dt then 1 else 0 end) over(partition by c.companyid, c.scoring_dt) as opened_alerts_90d_cnt , first_value(case when a.rules like '%TM%' then trim('-' from substr(a.rules, 0,6)) when a.rules like '%FRAUD%' then trim('-' from substr(a.rules, 0,8)) else a.rules end) over (partition by c.companyid, c.scoring_dt order by a.risedon_dt desc) as last_alert_name , coalesce(max(case when a.status in ('Good', 'Bad', 'TruePositive', 'FalsePositive', 'Archived') and resolvedon_dt <= c.scoring_dt then a.risedon_dt end) over (partition by c.companyid, c.scoring_dt), '2000-01-01 00:00:00') as last_closed_alert_dt , a.risedon_dt , a.resolvedon_dt from companies_to_score as c left join tt_alerts a on c.companyid = a.companyid and a.risedon_dt <= c.scoring_dt and a.risedon_dt >= c.scoring_dt - interval '90 days' )t), rules as ( select distinct a.companyid , a.partnerbank , a.amount_num , a.scoring_dt , a.legalform , a.finom_lt_days_cnt , a.countrycode , a.city , a.businesstype as business_type , a.rules , a.rules_norm , a.opened_alerts_90d_cnt , a.opened_last_alerts_90d_cnt , a.last_alert_name from alerts_for_sample a ), al as ( select a.companyid , a.scoring_dt , count(a.rules_norm) as tm_alert_cnt , count(distinct a.rules_norm) as unique_tm_alert_cnt , max(case when a.status in ('TruePositive', 'Bad') and a.rules = 'FCRM FP-1 PROD' then 1 else 0 end) as has_fp1 , max(case when a.status in ('TruePositive', 'Bad') and a.rules = 'FCRM FP-2 PROD' then 1 else 0 end) as has_fp2 , max(case when a.status in ('TruePositive', 'Bad') and a.rules = 'FCRM FP-3 PROD' then 1 else 0 end) as has_fp3 , max(case when a.status in ('TruePositive', 'Bad') and a.rules = 'FCRM FP-4 PROD' then 1 else 0 end) as has_fp4 , max(case when a.status in ('TruePositive', 'Bad') and a.rules = 'FCRM FP-5 PROD' then 1 else 0 end) as has_fp5 , max(case when a.status in ('TruePositive', 'Bad') and a.rules = 'FCRM FP-7 PROD' then 1 else 0 end) as has_fp7 , max(case when a.status in ('TruePositive', 'Bad') and a.rules = 'FCRM FP-9 PROD' then 1 else 0 end) as has_fp9 , max(case when a.status in ('TruePositive', 'Bad') and a.rules like '%FCRM FP-%' then 1 else 0 end) as has_fp , max(case when a.status in ('TruePositive', 'Bad') and a.rules like '%RAI%' then 1 else 0 end) as has_rai , sum(case when a.status in ('TruePositive', 'Bad') then 1 else 0 end)::integer as tp_cnt , sum(case when a.status in ('FalsePositive', 'Good') then 1 else 0 end)::integer as fp_cnt from alerts_for_sample a group by 1, 2 ) select distinct a.companyid , a.partnerbank , a.amount_num , a.scoring_dt , a.legalform , a.finom_lt_days_cnt , a.countrycode , a.city , a.business_type , a.rules , a.rules_norm , al.has_fp1 , al.has_fp2 , al.has_fp3 , al.has_fp4 , al.has_fp5 , al.has_fp7 , al.has_fp9 , al.has_fp , al.has_rai , al.tp_cnt , al.fp_cnt , a.opened_alerts_90d_cnt , a.opened_last_alerts_90d_cnt , a.last_alert_name from rules a left join al on a.companyid = al.companyid and a.scoring_dt = al.scoring_dt; return query select * from tt_alerts_agg; end; $$ LANGUAGE plpgsql;