· 6 years ago · Jul 24, 2019, 10:28 AM
1drop table mla_off_origin;
2create table mla_off_origin
3distkey(scoring_id)
4sortkey(scoring_id) as
5select ctx.*,
6 stc.provider_id,
7 stc.profile_id as stc_profile,
8 pcc.pay_status_id,
9 pcc.pay_status_detail_code
10from scoring_mi.context ctx
11inner join scoring.scoring_to_cust stc on stc.sc_cust_id=ctx.scoring_id
12inner join scoring.bt_mp_pay_payments pcc on pcc.pay_payment_id=stc.ref_id_nw
13where 1=1
14and ctx.site_id='MLA'
15and ctx.config_id in ('OFF','GTW')
16and stc.flow_type='MI'
17and ctx.op_creation_date>=date'2019-01-01'
18and ctx.receiver_ids is null
19and ctx.industry_id<>'test_low'
20and stc.provider_id<>'mock'
21;
22
23--
24
25
26
27
28drop table fcc_mla;
29create table fcc_mla
30distkey(scoring_id)
31sortkey(scoring_id) as
32select ctx.scoring_id,
33 count(1) qty,
34 sum(case when ctx.op_creation_date < dateadd(day,1,cast(fcc.creation_date as date)) then 1 else 0 end) qty_1d,
35 sum(case when ctx.op_creation_date < dateadd(day,3,cast(fcc.creation_date as date)) then 1 else 0 end) qty_3d,
36 sum(case when ctx.op_creation_date < dateadd(day,7,cast(fcc.creation_date as date)) then 1 else 0 end) qty_7d,
37 sum(case when ctx.op_creation_date < dateadd(day,15,cast(fcc.creation_date as date)) then 1 else 0 end) qty_15d
38from mla_off_origin ctx
39inner join scoring_mi.form_cc_data fcc on ctx.scoring_id = fcc.scoring_id
40where 1=1
41group by 1
42;
43
44
45
46drop table fcc_tc_dist_mla;
47create table fcc_tc_dist_mla
48distkey(scoring_id)
49sortkey(scoring_id) as
50select ctx.scoring_id,
51 count(1) qty,
52 count(distinct(case when ctx.hash_cc_number<>fcc.cc_hash_number and ctx.op_creation_date < dateadd(day,1,cast(fcc.creation_date as date)) then fcc.cc_hash_number else null end)) dist_tcs_1d
53from mla_off_origin ctx
54inner join scoring_mi.form_cc_data fcc on ctx.scoring_id = fcc.scoring_id
55where 1=1
56group by 1
57;
58
59
60drop table fcc_tc_mla;
61create table fcc_tc_mla
62distkey(scoring_id)
63sortkey(scoring_id) as
64select ctx.scoring_id,
65 count(1) qty,
66 sum(case when ctx.op_creation_date < dateadd(day,1,cast(fcc.creation_date as date)) then 1 else 0 end) qty_1d,
67 sum(case when ctx.op_creation_date < dateadd(day,3,cast(fcc.creation_date as date)) then 1 else 0 end) qty_3d,
68 sum(case when ctx.op_creation_date < dateadd(day,7,cast(fcc.creation_date as date)) then 1 else 0 end) qty_7d,
69 sum(case when ctx.op_creation_date < dateadd(day,15,cast(fcc.creation_date as date)) then 1 else 0 end) qty_15d
70from mla_off_origin ctx
71inner join scoring_mi.form_cc_data_tc fcc on ctx.scoring_id = fcc.scoring_id
72where 1=1
73group by 1
74;
75
76drop table fcc_ship_mla;
77create table fcc_ship_mla
78distkey(scoring_id)
79sortkey(scoring_id) as
80select ctx.scoring_id,
81 count(1) qty,
82 sum(case when ctx.op_creation_date < dateadd(day,1,cast(fcc.creation_date as date)) then 1 else 0 end) qty_1d,
83 sum(case when ctx.op_creation_date < dateadd(day,3,cast(fcc.creation_date as date)) then 1 else 0 end) qty_3d,
84 sum(case when ctx.op_creation_date < dateadd(day,7,cast(fcc.creation_date as date)) then 1 else 0 end) qty_7d,
85 sum(case when ctx.op_creation_date < dateadd(day,15,cast(fcc.creation_date as date)) then 1 else 0 end) qty_15d
86from mla_off_origin ctx
87inner join scoring_mi.form_cc_data_shipping fcc on ctx.scoring_id = fcc.scoring_id
88where 1=1
89group by 1
90;
91
92
93drop table fcc_dev_mla;
94create table fcc_dev_mla
95distkey(scoring_id)
96sortkey(scoring_id) as
97select ctx.scoring_id,
98 count(1) qty,
99 sum(case when ctx.op_creation_date < dateadd(day,1,cast(fcc.creation_date as date)) then 1 else 0 end) qty_1d,
100 sum(case when ctx.op_creation_date < dateadd(day,3,cast(fcc.creation_date as date)) then 1 else 0 end) qty_3d,
101 sum(case when ctx.op_creation_date < dateadd(day,7,cast(fcc.creation_date as date)) then 1 else 0 end) qty_7d,
102 sum(case when ctx.op_creation_date < dateadd(day,15,cast(fcc.creation_date as date)) then 1 else 0 end) qty_15d
103from mla_off_origin ctx
104inner join scoring_mi.form_cc_data_device_ml fcc on ctx.scoring_id = fcc.scoring_id
105where 1=1
106group by 1
107;
108
109
110drop table if exists new_fcc_card_mla;
111create table new_fcc_card_mla as
112select so.scoring_id,
113 fcc.distance sum_name,
114 case when fcc.payments_quantity is not null then fcc.payments_quantity
115 when fcc.approved_payments_quantity is not null then fcc.approved_payments_quantity
116 else 0 end qty,
117 1.0*sum_name*qty suma
118from mla_off_origin so
119inner join scoring_mi.context ctx on so.scoring_id = ctx.scoring_id
120left outer join scoring_mi.card_holder_name fcc on so.scoring_id = fcc.scoring_id
121where 1=1
122and upper(ctx.sender_add_name) not in ('SPOTIFY USER', 'CARD HOLDER NAME', 'CARDHOLDER_NAME','CLASICA','NOMINA PREFERENTE', 'PERFIL EJECUTIVO','PERFIL EDUCATIVO','IMPULSO NOMINA')
123group by 1,2,3
124;
125
126drop table casos_dist_tc_new_2_mla;
127create table casos_dist_tc_new_2_mla as
128select so.scoring_id,
129 1.0*sum(suma)/(sum(qty)+0.0001) avg_card
130from mla_off_origin so
131inner join new_fcc_card_mla dt on dt.scoring_id = so.scoring_id
132group by 1
133;
134
135drop table card_holder_multi_mla;
136create table card_holder_multi_mla as
137select distinct so.scoring_id
138from mla_off_origin so
139inner join casos_dist_tc_new_2_mla dt on dt.scoring_id = so.scoring_id
140where avg_card < 50
141group by 1
142;
143
144drop table fraudlist2_mla;
145create table fraudlist2_mla
146distkey(scoring_id)
147sortkey(scoring_id) as
148select ctx.scoring_id,
149 sum(case when type='tc' then 1 else 0 end) tc_fraudlist,
150 sum(case when type='doc' then 1 else 0 end) doc_fraudlist,
151 sum(case when type='cust' then 1 else 0 end) cust_fraudlist,
152 sum(case when type='zip_code' then 1 else 0 end) zc_fraudlist,
153 sum(case when type='device_ml' then 1 else 0 end) dev_fraudlist,
154 sum(case when type='ip' then 1 else 0 end) ip_fraudlist,
155 sum(case when type='address_line' then 1 else 0 end) add_fraudlist
156from mla_off_origin ctx
157inner join scoring_mi.fraudlist fl on ctx.scoring_id = fl.scoring_id and ctx.sender_id<>fl.user_id
158where 1=1
159and (action_reason in ('chargeback','chargeback_low','chargeback_high','chargeback_comp','fraude_cruce','fraude_mp','fraude_tc','money_in_tc','tko') or user_reason in ('chargeback','chargeback_low','chargeback_high','chargeback_comp','fraude_cruce','fraude_mp','fraude_tc','money_in_tc','tko'))
160group by 1
161;
162
163drop table cross_dev_mla;
164create table cross_dev_mla
165distkey(scoring_id)
166sortkey(scoring_id) as
167select ctx.scoring_id,
168 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%MONEY_IN_TC%' then user_id else null end)) auc_money_in_tc,
169 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%FRAUDE_TC%' then user_id else null end)) auc_fraude_tc,
170 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%CHARGEBACK_COMP%' then user_id else null end)) auc_chargeback_comp,
171 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%FRAUDE_CR%' then user_id else null end)) auc_fraude_cruce,
172 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%FRAUDE_ML%' then user_id else null end)) auc_fraude_ml,
173 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%TKO%' then user_id else null end)) auc_tko,
174 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%CHARGEBACK%' then user_id else null end)) auc_cbk_low,
175 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%RET_MP%' then user_id else null end)) auc_ret_mp,
176 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%MCONTROL%' then user_id else null end)) auc_mcontrol,
177 count(distinct(case when ctx.sender_id<>auc.user_id and mp_restrictions like '%ALERTA_DISPUTAS%' then user_id else null end)) auc_alert_disp
178from mla_off_origin ctx
179inner join scoring_mi.account_uniqueness_cross auc on ctx.scoring_id = auc.scoring_id
180where 1=1
181and auc.data_type = 'device_ml'
182group by 1
183;
184
185drop table fraudlist_mla;
186create table fraudlist_mla
187distkey(scoring_id)
188sortkey(scoring_id) as
189select ctx.scoring_id,
190 sum(case when type='tc' then 1 else 0 end) tc_fraudlist,
191 sum(case when type='doc' then 1 else 0 end) doc_fraudlist,
192 sum(case when type='cust' then 1 else 0 end) cust_fraudlist,
193 sum(case when type='zip_code' then 1 else 0 end) zc_fraudlist,
194 sum(case when type='device_ml' then 1 else 0 end) dev_fraudlist,
195 sum(case when type='ip' then 1 else 0 end) ip_fraudlist,
196 sum(case when type='address_line' then 1 else 0 end) add_fraudlist
197from mla_off_origin ctx
198inner join scoring_mi.fraudlist fl on ctx.scoring_id = fl.scoring_id and ctx.sender_id=fl.user_id
199where 1=1
200and (action_reason in ('chargeback','chargeback_low','chargeback_high','chargeback_comp','fraude_cruce','fraude_mp','fraude_tc','money_in_tc','tko') or user_reason in ('chargeback','chargeback_low','chargeback_high','chargeback_comp','fraude_cruce','fraude_mp','fraude_tc','money_in_tc','tko'))
201group by 1
202;
203
204
205drop table bin_mla;
206create table bin_mla
207distkey(scoring_id)
208sortkey(scoring_id) as
209select ctx.scoring_id,
210 sum(case when country_name not in ('ARGENTINA') then 1 else 0 end) is_foreign_bin
211from mla_off_origin ctx
212left outer join scoring_mi.BIN_CARD cus on ctx.scoring_id = cus.scoring_id
213where 1=1
214group by 1
215;
216
217drop table fcc_dev_acc_mla;
218create table fcc_dev_acc_mla
219distkey(scoring_id)
220sortkey(scoring_id) as
221select ctx.scoring_id,
222 count(distinct fcc.cust_id) acc_qty
223from mla_off_origin ctx
224inner join scoring_mi.form_cc_data_device_ml fcc on ctx.scoring_id = fcc.scoring_id and fcc.cust_id<>ctx.sender_id
225where 1=1
226and datediff(day, fcc.sender_site_since, ctx.op_creation_date)<=7
227group by 1
228;
229
230drop table fcc_tc_acc_mla;
231create table fcc_tc_acc_mla
232distkey(scoring_id)
233sortkey(scoring_id) as
234select ctx.scoring_id,
235 count(distinct fcc.cust_id) acc_qty
236from mla_off_origin ctx
237inner join scoring_mi.form_cc_data_tc fcc on ctx.scoring_id = fcc.scoring_id and fcc.cust_id<>ctx.sender_id
238where 1=1
239and datediff(day, fcc.sender_site_since, ctx.op_creation_date)<=7
240group by 1
241;
242
243drop table site_since_mla;
244create table site_since_mla
245distkey(scoring_id)
246sortkey(scoring_id) as
247select ctx.scoring_id,
248 sum(datediff(second, cus.site_since, ctx.op_creation_date)) seconds_btw
249from mla_off_origin ctx
250left outer join scoring_mi.customers cus on ctx.scoring_id = cus.scoring_id and cus.customer_role = 'S'
251where 1=1
252group by 1
253;
254
255drop table fcc_tc_1;
256create table fcc_tc_1
257distkey(scoring_id)
258sortkey(scoring_id) as
259select ctx.scoring_id,
260 count(1) qty,
261 sum(case when ctx.op_creation_date < dateadd(day,2,cast(fcc.creation_date as date)) then 1 else 0 end) tc_qty_48h
262from mla_off_origin ctx
263inner join scoring_mi.form_cc_data_tc fcc on ctx.scoring_id = fcc.scoring_id
264where 1=1
265group by 1
266;
267
268drop table tko_1;
269create table tko_1 as
270select distinct ctx.scoring_id
271from scoring_mi.context ctx
272inner join mla_off_origin so on so.scoring_id=ctx.scoring_id
273left outer join scoring_mi.vector_fcc_shipping fcc_ship on ctx.scoring_id=fcc_ship.scoring_id
274left outer join scoring_mi.customers cus on ctx.scoring_id=cus.scoring_id and cus.customer_role='S'
275left outer join fcc_tc_1 fcc on ctx.scoring_id=fcc.scoring_id
276left outer join scoring_mi.device_ml dml on ctx.scoring_id=dml.scoring_id
277where 1=1
278and (phsa_qty_app_7d>1 or phsa_qty_users_7d>1)
279and (datediff(days, cus.site_since, ctx.op_creation_date)>180)
280and datediff(days, dml.creation_date, ctx.op_creation_date)< 30
281and (tc_qty_48h>1 or ctx.op_dol_amount>50 or phsa_qty_users_1d>0)
282and ctx.has_approved_device_ml<>'Y'
283and ctx.has_approved_payment_tc<>'Y'
284;
285
286drop table tko_2;
287create table tko_2 as
288select distinct ctx.scoring_id
289from scoring_mi.context ctx
290inner join mla_off_origin so on so.scoring_id=ctx.scoring_id
291left outer join scoring_mi.customers cus on ctx.scoring_id=cus.scoring_id and cus.customer_role='S'
292left outer join scoring_mi.device_ml dml on ctx.scoring_id=dml.scoring_id
293where 1=1
294and (datediff(days, cus.site_since, ctx.op_creation_date)>180)
295and datediff(days, dml.creation_date, ctx.op_creation_date)< 30
296and ctx.has_approved_device_ml<>'Y'
297and ctx.has_approved_payment_tc<>'Y'
298;
299
300create table tko_3 as
301select distinct ctx.scoring_id
302from scoring_mi.context ctx
303inner join mla_off_origin so on so.scoring_id=ctx.scoring_id
304left outer join scoring_mi.vector_fcc_tc fcc on ctx.scoring_id=fcc.scoring_id
305left outer join scoring_mi.device_ml dml on ctx.scoring_id=dml.scoring_id
306where 1=1
307and datediff(days, dml.creation_date, ctx.op_creation_date)< 30
308and ctx.has_approved_device_ml<>'Y'
309and ctx.has_approved_payment_tc<>'Y'
310and phtc_amt_app_acc_3d+ctx.op_dol_amount>150
311;
312
313create table tko_4 as
314select distinct ctx.scoring_id
315from scoring_mi.context ctx
316inner join mla_off_origin so on so.scoring_id=ctx.scoring_id
317left outer join scoring_mi.vector_fcc_tc fcc on ctx.scoring_id=fcc.scoring_id
318left outer join scoring_mi.device_ml dml on ctx.scoring_id=dml.scoring_id
319where 1=1
320and datediff(days, dml.creation_date, ctx.op_creation_date)< 30
321and ctx.has_approved_device_ml<>'Y'
322and ctx.has_approved_payment_tc<>'Y'
323and ctx.op_dol_amount>50
324
325
326--- is_lo_sr
327
328drop table spr_tmp_2;
329create table spr_tmp_3 as
330select stc.scoring_id
331from mla_off_origin stc
332inner join scoring.scoring_provider_result sr on sr.scoring_id = stc.scoring_id and sr.scoring_provider='strong_rules' and sr.profile_id = 'LOW'
333;
334---
335
336select case when (fcc_mla.qty_1d>2 or fcc_tc_dist_mla.dist_tcs_1d>2 or fcc_tc_mla.qty_1d>2 or fcc_ship_mla.qty_1d>1 or fcc_dev_mla.qty_1d>2
337 or fraudlist2_mla.zc_fraudlist>0 or fraudlist2_mla.tc_fraudlist>0 or fraudlist2_mla.doc_fraudlist>0
338 or site_since_mla.seconds_btw<=60
339 or tko_1.scoring_id is not null
340 or tko_2.scoring_id is not null
341 or tko_3.scoring_id is not null
342 or tko_4.scoring_id is not null
343 or (auc_money_in_tc>0 or auc_fraude_tc>0 or auc_chargeback_comp>0 or auc_fraude_cruce>0 or auc_fraude_ml>0 or auc_tko>0 or auc_cbk_low>0 or auc_ret_mp>0 or auc_mcontrol>0 or auc_alert_disp>0)
344 or fraudlist_mla.tc_fraudlist>0 or fraudlist_mla.doc_fraudlist>0 or fraudlist_mla.cust_fraudlist or fraudlist_mla.dev_fraudlist>0 or is_foreign_bin>0 or fcc_tc_acc_mla.acc_qty>0 or fcc_dev_acc_mla.acc_qty>0 or card_holder_multi_mla.scoring_id is not null) then 1 else 0 end dist_tc_qty,
345 count(*),
346 100.00*count(*)/sum(count(1)) over () per
347from mla_off_origin ctx
348left outer join tko_1 on tko_1.scoring_id=ctx.scoring_id
349left outer join tko_2 on tko_2.scoring_id=ctx.scoring_id
350left outer join tko_3 on tko_3.scoring_id=ctx.scoring_id
351left outer join tko_4 on tko_4.scoring_id=ctx.scoring_id
352left outer join spr_tmp_3 spr on spr.scoring_id=ctx.scoring_id
353left outer join fcc_mla fcc_mla on ctx.scoring_id = fcc_mla.scoring_id
354left outer join fcc_tc_dist_mla on ctx.scoring_id=fcc_tc_dist_mla.scoring_id
355left outer join fcc_tc_mla on ctx.scoring_id = fcc_tc_mla.scoring_id
356left outer join fcc_ship_mla fcc_ship_mla on ctx.scoring_id = fcc_ship_mla.scoring_id
357left outer join fcc_dev_mla fcc_dev_mla on ctx.scoring_id = fcc_dev_mla.scoring_id
358left outer join site_since_mla on ctx.scoring_id = site_since_mla.scoring_id
359left outer join card_holder_multi_mla on ctx.scoring_id = card_holder_multi_mla.scoring_id
360left outer join fraudlist2_mla on ctx.scoring_id = fraudlist2_mla.scoring_id
361left outer join cross_dev_mla on ctx.scoring_id = cross_dev_mla.scoring_id
362left outer join fraudlist_mla on ctx.scoring_id = fraudlist_mla.scoring_id
363left outer join bin_mla on ctx.scoring_id = bin_mla.scoring_id
364left outer join fcc_tc_acc_mla on ctx.scoring_id=fcc_tc_acc_mla.scoring_id
365left outer join fcc_dev_acc_mla on ctx.scoring_id=fcc_dev_acc_mla.scoring_id
366where 1=1
367and ((ctx.stc_profile='MIDHIGH' or ctx.first_scoring_provider='mantika'))-- and spr.scoring_id is not null) --bloq models
368--and (ctx.stc_profile<>'LOW' or ctx.first_scoring_provider='mantika') --bloq all
369--and ctx.pay_status_id = 'rejected' and ctx.pay_status_detail_code = 'cc_rejected_high_risk' --rejected
370--and ((ctx.sow_profile='LOW' or ctx.first_scoring_provider<>'mantika') and spr.scoring_id is not null) --low models
371--and (ctx.sow_profile='LOW' or ctx.first_scoring_provider<>'mantika') --low all
372--and ctx.pay_status_id in ('approved','in_mediation','refunded','charged_back') --app
373and ctx.receiver_id in (272858420,381947421,177932929,425402893,164352247,246862598)
374and ctx.op_creation_date>=date'2019-05-01'
375group by 1
376order by 2 desc
377;
378
379
380select date_trunc('month',op_creation_date) sco_month, receiver_id, 100.00*sum(case when (ctx.stc_profile<>'LOW' or ctx.first_scoring_provider='mantika') then 1 else 0 end)/count(*) bloq_rate
381from mla_off_origin ctx
382where 1=1
383and ctx.op_creation_date>=date'2018-11-01'
384group by 1,2
385order by 1,2;
386
387select * from scoring.scoring_to_cust
388where ref_id_nw=4658916650;
389
390
391
392
393
394select top 1000 scoring_id from (
395select ctx.scoring_id,
396 case when (fcc_mla.qty_1d>2 or fcc_tc_dist_mla.dist_tcs_1d>2 or fcc_tc_mla.qty_1d>2 or fcc_ship_mla.qty_1d>1 or fcc_dev_mla.qty_1d>2
397 or fraudlist2_mla.zc_fraudlist>0 or fraudlist2_mla.tc_fraudlist>0 or fraudlist2_mla.doc_fraudlist>0
398 or site_since_mla.seconds_btw<=60
399 or (auc_money_in_tc>0 or auc_fraude_tc>0 or auc_chargeback_comp>0 or auc_fraude_cruce>0 or auc_fraude_ml>0 or auc_tko>0 or auc_cbk_low>0 or auc_ret_mp>0 or auc_mcontrol>0 or auc_alert_disp>0)
400 or fraudlist_mla.tc_fraudlist>0 or fraudlist_mla.doc_fraudlist>0 or fraudlist_mla.cust_fraudlist or fraudlist_mla.dev_fraudlist>0 or is_foreign_bin>0 or fcc_tc_acc_mla.acc_qty>0 or fcc_dev_acc_mla.acc_qty>0 or card_holder_multi_mla.scoring_id is not null) then 1 else 0 end dist_tc_qty
401from mla_off_origin ctx
402left outer join spr_tmp_3 spr on spr.scoring_id=ctx.scoring_id
403left outer join fcc_mla fcc_mla on ctx.scoring_id = fcc_mla.scoring_id
404left outer join fcc_tc_dist_mla on ctx.scoring_id=fcc_tc_dist_mla.scoring_id
405left outer join fcc_tc_mla on ctx.scoring_id = fcc_tc_mla.scoring_id
406left outer join fcc_ship_mla fcc_ship_mla on ctx.scoring_id = fcc_ship_mla.scoring_id
407left outer join fcc_dev_mla fcc_dev_mla on ctx.scoring_id = fcc_dev_mla.scoring_id
408left outer join site_since_mla on ctx.scoring_id = site_since_mla.scoring_id
409left outer join card_holder_multi_mla on ctx.scoring_id = card_holder_multi_mla.scoring_id
410left outer join fraudlist2_mla on ctx.scoring_id = fraudlist2_mla.scoring_id
411left outer join cross_dev_mla on ctx.scoring_id = cross_dev_mla.scoring_id
412left outer join fraudlist_mla on ctx.scoring_id = fraudlist_mla.scoring_id
413left outer join bin_mla on ctx.scoring_id = bin_mla.scoring_id
414left outer join fcc_tc_acc_mla on ctx.scoring_id=fcc_tc_acc_mla.scoring_id
415left outer join fcc_dev_acc_mla on ctx.scoring_id=fcc_dev_acc_mla.scoring_id
416where 1=1
417--and spr.scoring_id is null
418--and ((ctx.stc_profile='MIDHIGH' or ctx.first_scoring_provider='mantika') and spr.scoring_id is not null) --bloq models
419and (ctx.stc_profile<>'LOW' or ctx.first_scoring_provider='mantika')-- and not ((ctx.stc_profile='MIDHIGH' or ctx.first_scoring_provider='mantika') and spr.scoring_id is not null)
420and ctx.op_creation_date>=date'2019-01-01'
421and ctx.receiver_id in (272858420)
422) t
423where dist_tc_qty=0
424;
425
426select distinct industry, count(*) from scoring.scoring_to_cust
427where sc_cust_id in (select scoring_id from mla_off_origin where receiver_id in (272858420,164352247,381947421,298114439,425402893,164352247,246862598)) --
428group by 1
429;
430
431select receiver_id, stv.tree_id, stv.tree_version_id, so.provider_id, flow_type, stc.config_id, count(*)
432from mla_off_origin so
433inner join scoring.scoring_to_cust stc on stc.sc_cust_id=so.scoring_id
434inner join scoring.scoring_provider_result spr on spr.scoring_id = so.scoring_id and spr.scoring_provider=so.provider_id
435inner join scoring.scoring_tree_version stv on spr.external_id = stv.scoring_id
436where 1=1
437and spr.scoring_provider not in ('strong_rules','mantika')
438and receiver_id in (272858420,164352247,381947421,298114439,425402893,164352247,246862598)
439and so.op_creation_date>=date'2019-06-01'
440group by 1,2,3,4,5,6
441order by 1,2,3,4,5,6
442
443
444select receiver_id, stv.tree_id, stv.tree_version_id, so.provider_id, flow_type, stc.config_id, count(*)
445from mla_off_origin so
446inner join scoring.scoring_to_cust stc on stc.sc_cust_id=so.scoring_id
447inner join scoring.scoring_provider_result spr on spr.scoring_id = so.scoring_id and spr.scoring_provider=so.provider_id
448inner join scoring.scoring_tree_version stv on spr.external_id = stv.scoring_id
449where 1=1
450and spr.scoring_provider in ('strong_rules')
451and receiver_id in (272858420,164352247,381947421,298114439,425402893,164352247,246862598)
452and so.op_creation_date>=date'2019-06-01'
453group by 1,2,3,4,5,6
454order by 1,2,3,4,5,6