INSERT INTO team_kingkong.tpap_cybercell_fraud_base3
select upi_txn_id_switch,
txn_date_switch,
txn_amount_switch,
payer_vpa_switch,
payee_vpa_switch,
payer_mcc_switch,
payee_mcc_switch,
payer_handle,
payee_handle,
scope_cust_id,
merchant_bene_type,
category,
payer_mobile_no,
upi_subtype,
reporting_date,
ingestion_date,
'Cybercell' as source
from
(select * from
(select DISTINCT transaction_id as txnid,
cast(txn_amount as double) as txn_amount,
DATE(reporting_date) as reporting_date,
DATE(dl_last_updated) AS ingestion_date,
row_number() over(partition by transaction_id) as txn_id_duplicate
FROM frauds.ppsl_cybercell_snapshot_v3
where DATE(dl_last_updated) BETWEEN DATE'2025-02-01' AND DATE'2025-02-28'
and transaction_id NOT IN ('', ' ', 'NA', 'N.A', '0') and transaction_id is not null)
where txn_id_duplicate = 1) b
inner join
(select distinct json_extract_scalar(payresultcontext, '$.payMethodResultList[0].extendInfo.payeeVpa') AS payee_vpa,
transactionid,
IF(rrncode IS NOT NULL, rrncode, banktxnid) as rrn_pg,
DATE(dl_last_updated) as dl_last_updated
FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
Where DATE(dl_last_updated) BETWEEN DATE'2024-12-01' AND DATE'2025-02-28' -- DATE_ADD(CURRENT_DATE, -90)
and date(gmt_occur_derived) BETWEEN DATE'2024-12-01' AND DATE'2025-02-28' -- DATE_ADD(CURRENT_DATE, -90)
and payresult in ('payment_success') and upper(paymethod) in ('UPI')) c
on b.txnid = c.transactionid AND c.dl_last_updated < b.reporting_date
INNER join
(SELECT * FROM
(select DISTINCT b.created_on as txn_date_switch
,a.txn_id as upi_txn_id_switch
, a.vpa as payer_vpa_switch
, b.vpa as payee_vpa_switch
, a.amount as txn_amount_switch
,a.mcc as payer_mcc_switch
, b.mcc as payee_mcc_switch
,a.handle as payer_handle
, b.handle as payee_handle
,a.scope_cust_id
,a.mobile_no as payer_mobile_no
,(case when (lower(b.vpa) like '%@paytm%' or lower(b.vpa) like '%@pt%') then 'paytm merchant/bene' else 'others' end) merchant_bene_type
from switch.txn_participants_snapshot_v3 a
inner join switch.txn_participants_snapshot_v3 b on a.txn_id = b.txn_id
where DATE(a.dl_last_updated) BETWEEN DATE'2024-12-01' AND DATE'2025-02-28' -- DATE_ADD(CURRENT_DATE, -90)
and b.dl_last_updated BETWEEN DATE'2024-12-01' AND DATE'2025-02-28' -- DATE_ADD(CURRENT_DATE, -90)
and a.created_on BETWEEN DATE'2024-12-01' AND DATE'2025-02-28' -- DATE_ADD(CURRENT_DATE, -90)
and a.participant_type in ('PAYER') and b.participant_type in ('PAYEE')
and (lower(a.vpa) like'%@paytm%' or lower(a.vpa) like '%@pt%')) x
inner join
(select DISTINCT txn_id, category, rrn
from switch.txn_info_snapshot_v3
where DATE(dl_last_updated) BETWEEN DATE'2024-12-01' AND DATE'2025-02-28' -- DATE_ADD(CURRENT_DATE, -90)
and DATE(created_on) BETWEEN DATE'2024-12-01' AND DATE'2025-02-28' -- DATE_ADD(CURRENT_DATE, -90)
and upper(status) in ('SUCCESS')) f
on x.upi_txn_id_switch = f.txn_id)y
on y.rrn = c.rrn_pg and y.payee_vpa_switch = c.payee_vpa AND y.txn_date_switch < b.reporting_date
LEFT JOIN
(SELECT DISTINCT txnid
, regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
, CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) as amount
, json_extract_scalar(request, '$.requestPayload.payeeVpa') as payeeVpa
, DATE(dl_last_updated) as dl_last_updated
FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE'2024-12-01' AND DATE'2025-02-28'
AND json_extract_scalar(response, '$.action_recommended') = 'PASS'
AND json_extract_scalar(request, '$.source') = 'UPI'
AND (lower(json_extract_scalar(request, '$.requestPayload.payerVpa')) LIKE '%@paytm%'
or lower(json_extract_scalar(request, '$.requestPayload.payerVpa')) like '%@pt%'))d
on y.upi_txn_id_switch = d.txnid AND y.txn_amount_switch = d.amount AND c.payee_vpa = d.payeeVpa AND d.dl_last_updated < b.reporting_date
;
