ONUS : UtilityEventTxnLImit

-- UtilityEventTxnLImit
DROP TABLE team_kingkong.onus_UtilityEventTxnLImit_breaches;

-- CREATE TABLE team_kingkong.onus_UtilityEventTxnLImit_breaches AS 
INSERT INTO team_kingkong.onus_UtilityEventTxnLImit_breaches 
SELECT A.*, case when m1.mid is not null then category else 'Others' end as business_category
, 1000001 as per_txn_threshold
, 'per txn threshold breached' as breach_reason
FROM 
    (select distinct userid, transactionid,
    cast(eventAmount as double) / 100 as amt,
    dateinserted,
    substr(cast(dateinserted as varchar(30)), 1, 7) as mnth,
    paymethod, paytmmerchantid, responsestatus, actionrecommended
    FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
    AND SOURCE = 'PG' AND paymethod IN ('DEBIT_CARD', 'CREDIT_CARD')
AND responsestatus = 'SUCCESS' AND actionrecommended <> 'BLOCK'
    AND (cast(eventAmount as double) / 100) > 1000001
    AND eventid IN (SELECT eventlinkid
    FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
    WHERE dl_last_updated BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
    AND payresult = 'payment_success')) a
left join
    (select * from team_kingkong.voc_mid_categorization where mid != '') m1
on a.paytmmerchantid = m1.mid;