rfi 12 ctoc

WITH user_to_user_txn AS (
    SELECT 
        receiveruser,
        senderuserid,
        LOWER(sendernameonbankaccount) AS lower_sendername,
        COUNT(DISTINCT transaction_id) AS total_send_txns,
        SPLIT(Lower(sendernameonbankaccount), " ") AS array3,
        SUM(totaltransactionamount) AS total_send_amt
    FROM 
        fraud.transaction_details_v3
    WHERE 
        updated_date BETWEEN DATE_SUB('2023-10-02', 32) AND DATE_SUB('2023-10-02', 2)
        AND pay_transaction_status = 'COMPLETED'
        AND sendertype = 'INTERNAL_USER'
        AND workflowtype IN ('CONSUMER_TO_CONSUMER', 'CONSUMER_TO_CONSUMER_V2')
        AND transfermode IN ('PEER_TO_PEER')
        AND receiveruser <> senderuserid
    GROUP BY receiveruser, senderuserid, LOWER(sendernameonbankaccount)
),

receiver_txn AS (
    SELECT 
        receiver_identity, 
        LOWER(account_holder_name) AS lower_account_holder_name,
        SPLIT(LOWER(account_holder_name), " ") AS array1
    FROM  
        payment.transaction_receiver_instruments 
    WHERE 
        year = 2023 AND month = 09
    GROUP BY receiver_identity, LOWER(account_holder_name)
),

user_txn AS (
    SELECT 
        receiveruser,
        LOWER(receiver_name) AS lower_receiver_name,
        COUNT(DISTINCT transaction_id) AS user_txns_all_30d,
        SUM(totaltransactionamount) AS user_received_amt_all_30d
    FROM 
        fraud.transaction_details_v3
    WHERE 
        updated_date BETWEEN DATE_SUB('2023-10-02', 32) AND DATE_SUB('2023-10-02', 2)
        AND pay_transaction_status = 'COMPLETED'
        AND transfermode IN ('PEER_TO_PEER')
        AND workflowtype IN ('CONSUMER_TO_CONSUMER', 'CONSUMER_TO_CONSUMER_V2')
    GROUP BY receiveruser, LOWER(receiver_name)
),

sub_tb1 AS (
    SELECT 
        a.senderuserid,
        a.receiveruser,
        total_send_txns,
        b.user_txns_all_30d,
        b.user_received_amt_all_30d,
        a.total_send_amt,
        c.receiver_identity,
        a.array3,
        c.array1,
        ARRAY_CONTAINS(a.array3, c.array1[0]) OR
        ARRAY_CONTAINS(a.array3, c.array1[1]) OR
        ARRAY_CONTAINS(a.array3, c.array1[2]) OR
        ARRAY_CONTAINS(a.array3, c.array1[3]) AS true_falsecol2
    FROM 
        user_to_user_txn a
    LEFT JOIN 
        user_txn b ON a.receiveruser = b.receiveruser
    LEFT JOIN 
        receiver_txn c ON b.receiveruser = c.receiver_identity -- Add missing alias 'c'
    WHERE 
        total_send_txns > 200 AND total_send_txns / b.user_txns_all_30d > 0.7 and c.array1 IS NOT NULL
),

final_tb1 AS (
    SELECT *
    FROM sub_tb1
    WHERE true_falsecol2 = false -- Fix column name in WHERE clause
)

SELECT *
FROM final_tb1;
SELECT DATE_SUB('2023-12-02',32) starting_date
,DATE_SUB('2023-12-02',2) ending_date
,receiveruser AS identifier
,'NA' as active_days
,total_send_amt AS value
,'Offline-FIU-13-C2M-Large value of transactions from a single customer' AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' as type_fra
,'User' as issue_type
,'UPI' as sub_issue_type
,concat ('total_send_txns :', total_send_txns, ',merchant_txns_all_30d:' , merchant_txns_all_30d,'senderuserid:' , senderuserid) AS comment
from final_tb1 ;