OFFUS : edc_card_velocity_amount

-- CREATE TABLE team_kingkong.offus_edc_card_velocity_amount_breaches AS
INSERT INTO team_kingkong.offus_edc_card_velocity_amount_breaches
with offus_txn as
(SELECT DISTINCT globalcardindex, transactionid, txn_amount, txn_date, paytmmerchantid, txn_timestamp FROM
    (SELECT DISTINCT pg_mid from cdo.total_offline_merchant_base_snapshot_v3) f
INNER join
    (select transactionid
    , cast(eventamount as double)/100 as txn_amount
    , paytmmerchantid
    , globalcardindex
    , DATE(dl_last_updated) AS txn_date
    , CAST(velocitytimestamp AS DOUBLE) AS txn_timestamp
    from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    where dl_last_updated BETWEEN date'2025-05-01' AND DATE'2025-05-31'
    and paymethod in ('CREDIT_CARD','DEBIT_CARD','EMI','EMI_DC')
    AND actionrecommended <> 'BLOCK' AND responsestatus = 'SUCCESS'
    AND paytmmerchantid IS NOT NULL AND paytmmerchantid <> '' AND paytmmerchantid <> ' '
    AND globalcardindex IS NOT NULL AND globalcardindex <> '' AND globalcardindex <> ' ') a
on a.paytmmerchantid = f.pg_mid
LEFT JOIN
    (SELECT mid AS edc_mid FROM paytmpgdb.entity_edc_info_snapshot_v3
    WHERE terminal_status = 'ACTIVE' AND dl_last_updated >= DATE '2010-01-01') b 
ON a.paytmmerchantid = b.edc_mid)

SELECT * FROM 
    (SELECT A.globalcardindex, A.transactionid, A.txn_amount, A.txn_date, A.paytmmerchantid, 'edc_card_velocity_amount' AS rule_name, A.txn_timestamp
    , SUM(IF((A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 21600000, B.txn_amount, NULL)) AS amt6_hr
    , 1000000 AS amt6_hr_threshold
    , SUM(B.txn_amount) as amt24_hr
    , 2000000 AS amt4_hr_threshold
    FROM
        (SELECT * FROM offus_txn
        WHERE txn_date  BETWEEN date'2025-05-01' AND DATE'2025-05-31')A
    INNER JOIN
        (SELECT * FROM offus_txn)B
    ON A.globalcardindex = b.globalcardindex AND A.paytmmerchantid = B.paytmmerchantid AND A.transactionid <> B.transactionid
    AND (A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 86400000 -- <= 1d
    GROUP BY 1,2,3,4,5,6,7)
WHERE ((amt6_hr + txn_amount) > 1000000) OR ((amt24_hr + txn_amount) > 2000000);