TPAP : RISK009
Tue Jul 29 2025 10:46:33 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi.b
-- RISK009 DROP TABLE team_kingkong.tpap_risk009_breaches; -- CREATE TABLE team_kingkong.tpap_risk009_breaches AS INSERT INTO team_kingkong.tpap_risk009_breaches with tpap_base as ( SELECT DISTINCT B.*, C.category, C.txn_type , IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype , D.payerType, D.payeeType FROM (SELECT txn_id, MAX(CASE WHEN participant_type = 'PAYER' THEN vpa END) AS payer_vpa, MAX(CASE WHEN participant_type = 'PAYEE' THEN vpa END) AS payee_vpa, MAX(DATE(created_on)) as txn_date, MAX(amount) AS txn_amount, MAX(created_on) AS txn_time FROM switch.txn_participants_snapshot_v3 WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31' AND DATE(created_on) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31' GROUP BY 1)B INNER join (select txn_id, category, "type" AS txn_type from switch.txn_info_snapshot_v3 where DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31' and DATE(created_on) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31' and upper(status) = 'SUCCESS' AND "type" = 'COLLECT' AND category = 'VPA2VPA') C on B.txn_id = C.txn_id INNER JOIN (SELECT txnid , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype , regexp_replace(cast(json_extract(request, '$.requestPayload.payerType') AS varchar),'"','') as payerType , regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') as payeeType FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3 WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '1' DAY) AND DATE'2025-01-31' AND (lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '')) LIKE '%@paytm%' or lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '')) like '%@pt%' OR lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '')) LIKE '%@paytm%' or lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '')) like '%@pt%') AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '') IS NOT NULL AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '') IS NOT NULL AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '') <> '' AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '') <> '' AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK' AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerType') AS varchar),'"','') = 'PERSON' AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') = 'PERSON' AND regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION')D ON B.txn_id = D.txnid WHERE payer_vpa IS NOT NULL AND payee_vpa IS NOT NULL ) SELECT *, 'upi_collect_request_p2p_vpa_v1' as rule_name, CASE WHEN txn_succ_24hr >= threshold_24hr AND txn_succ_week >= threshold_week THEN '24hr & week breach' WHEN txn_succ_24hr >= threshold_24hr THEN '24hr breach' WHEN txn_succ_week >= threshold_week THEN 'week breach' ELSE NULL END AS breach_reason FROM (SELECT t1.payer_vpa, t1.payee_vpa, t1.txn_id, t1.txn_amount, t1.category, t1.upi_subtype, t1.txn_time, DATE(t1.txn_time) AS txn_date , COUNT(DISTINCT IF(DATE(t2.txn_time) BETWEEN DATE(DATE(t1.txn_time) - INTERVAL '86400' SECOND) AND DATE(t1.txn_time), t2.txn_id, NULL)) AS txn_succ_24hr , 10 as threshold_24hr , COUNT(DISTINCT IF(DATE(t2.txn_time) BETWEEN DATE(DATE(t1.txn_time) - INTERVAL '604800' SECOND) AND DATE(t1.txn_time), t2.txn_id, NULL)) AS txn_succ_week , 25 as threshold_week FROM tpap_base t1 INNER JOIN tpap_base t2 ON t1.payee_vpa = t2.payee_vpa AND t1.payer_vpa = t2.payer_vpa AND t2.txn_time BETWEEN (t1.txn_time - INTERVAL '604800' SECOND) AND t1.txn_time AND t1.txn_id <> t2.txn_id AND t1.txn_date BETWEEN DATE'2025-01-01' AND DATE'2025-01-31' GROUP BY t1.payer_vpa, t1.payee_vpa, t1.txn_id, t1.txn_amount, t1.category, t1.upi_subtype, t1.txn_time, t1.txn_date) WHERE (txn_succ_24hr >= threshold_24hr) OR (txn_succ_week >= threshold_week) ;
Comments