ONUS : on_us_sbi_nb_limit

DROP TABLE team_kingkong.onus_on_us_sbi_nb_limit_breaches;

-- CREATE TABLE team_kingkong.onus_on_us_sbi_nb_limit_breaches AS 
INSERT INTO team_kingkong.onus_on_us_sbi_nb_limit_breaches 
SELECT A.*, case when m1.mid is not null then category else 'Others' end as business_category
, 100000 as per_txn_threshold
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(DATE'2025-03-01' - INTERVAL '30' DAY) AND DATE'2025-03-31'
    AND SOURCE = 'PG' AND actionrecommended <> 'BLOCK' AND responsestatus = 'SUCCESS'
    AND paymethod = 'NET_BANKING' AND apicodeoption = 'SBINC1IN_NET_BANKING_PAYMENT'
    AND paytmmerchantid IN ('PTMVIS48435535949128','PTMCBP11428987150800','PTMCBP84799392178473')
    AND (cast(eventAmount as double) / 100) > 100000
    AND eventid IN (SELECT eventlinkid
    FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
    WHERE dl_last_updated BETWEEN DATE(DATE'2025-03-01' - INTERVAL '30' DAY) AND DATE'2025-03-31')) a
left join
    (select * from team_kingkong.voc_mid_categorization where mid != '') m1
on a.paytmmerchantid = m1.mid;