tpap_cybercell_fraud_base2

CREATE TABLE team_kingkong.tpap_cybercell_fraud_base2 AS
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) >= DATE'2024-12-01'
            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
        FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
        Where DATE(dl_last_updated) >= DATE'2024-12-01' -- DATE_ADD(CURRENT_DATE, -90)
        and date(gmt_occur_derived) >= DATE'2024-12-01' -- DATE_ADD(CURRENT_DATE, -90)
        and payresult in ('payment_success') and upper(paymethod) in ('UPI')) c
    on b.txnid = c.transactionid
    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) >= DATE'2024-12-01' -- DATE_ADD(CURRENT_DATE, -90)
        and b.dl_last_updated >= DATE'2024-12-01' -- DATE_ADD(CURRENT_DATE, -90)
        and a.created_on >= DATE'2024-12-01' -- 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) >= DATE'2024-12-01' -- DATE_ADD(CURRENT_DATE, -90)
        and DATE(created_on) >= DATE'2024-12-01' -- 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
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
    FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
    WHERE DATE(dl_last_updated) >= DATE'2024-12-01' 
    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
;