OFFUS : ICA_PerCard_PerMID_TXN_Limit

-- ICA_PerCard_PerMID_TXN_Limit
-- DROP TABLE team_kingkong.offus_ICA_PerCard_PerMID_TXN_Limit_breaches;
 
CREATE TABLE team_kingkong.offus_ICA_PerCard_PerMID_TXN_Limit_breaches AS
-- INSERT INTO team_kingkong.offus_ICA_PerCard_PerMID_TXN_Limit_breaches
with offus_txn as
(SELECT globalcardindex, transactionid, txn_amount, txn_date, paytmmerchantid, txn_timestamp, paymethod
, case when edc_mid is not null then 'EDC' else 'QR' end as mid_type
, mcc, isindian, isedcrequest
, CASE WHEN mcc IN (5411, 5812, 9399, 8211, 7999, 7011, 5813, 4511, 8071, 8062) THEN 1 ELSE 0 END AS non_risky_high_mcc
, CASE WHEN mcc IN (5962, 7273, 7995, 5122, 6051, 6012, 5993, 5968, 5966, 5912, 6211, 5816, 4816, 5967) THEN 1 ELSE 0 END AS risky_mcc
FROM
    (SELECT DISTINCT pg_mid from cdo.total_offline_merchant_base_snapshot_v3) f
INNER join
    (select distinct 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
    , paymethod
    , isindian
    , isedcrequest
    from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    where dl_last_updated BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31' -- BETWEEN date'2025-01-31' AND
    and paymethod in ('CREDIT_CARD','DEBIT_CARD')
    AND actionrecommended <> 'BLOCK' AND responsestatus = 'SUCCESS') a
on a.paytmmerchantid = f.pg_mid
LEFT JOIN
    (SELECT DISTINCT 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
INNER JOIN
    (select distinct txn_id as pg_txn_id, mcc
    from dwh.pg_olap
    where ingest_date BETWEEN DATE'2025-01-01' AND DATE(DATE'2025-01-31' + INTERVAL '1' DAY)
    and txn_started_at BETWEEN  DATE'2025-01-01' AND DATE(DATE'2025-01-31' + INTERVAL '1' DAY)
    and txn_status = 'SUCCESS') d
on a.transactionid = d.pg_txn_id)
 
SELECT * FROM
    (SELECT a.globalcardindex, A.transactionid, A.txn_amount, A.txn_date, A.paytmmerchantid, A.txn_timestamp
    , A.mid_type, A.paymethod, A.mcc, A.non_risky_high_mcc, A.risky_mcc
    , COUNT(IF((A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 60000, B.transactionid, NULL)) AS txn1_min
    , CASE WHEN A.risky_mcc > 0 THEN 1
    WHEN A.non_risky_high_mcc > 0 THEN 1
    WHEN A.risky_mcc = 0 AND A.non_risky_high_mcc = 0 THEN 1
    END AS txn1_min_threshold
    , COUNT(IF((A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 86400000, B.transactionid, NULL)) AS txn1_day
    , CASE WHEN A.risky_mcc > 0 THEN 3
    WHEN A.non_risky_high_mcc > 0 THEN 5
    WHEN A.risky_mcc = 0 AND A.non_risky_high_mcc = 0 THEN 3
    END AS txn1_day_threshold
    , COUNT(IF((A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 604800000, B.transactionid, NULL)) AS txn7_day
    , CASE WHEN A.risky_mcc > 0 THEN 5
    WHEN A.non_risky_high_mcc > 0 THEN 10
    WHEN A.risky_mcc = 0 AND A.non_risky_high_mcc = 0 THEN 5
    END AS txn7_day_threshold
    , COUNT(IF((A.txn_timestamp - B.txn_timestamp) BETWEEN 0 AND 2592000000, B.transactionid, NULL)) AS txn30_day
    , CASE WHEN A.risky_mcc > 0 THEN 15
    WHEN A.non_risky_high_mcc > 0 THEN 25
    WHEN A.risky_mcc = 0 AND A.non_risky_high_mcc = 0 THEN 15 
    END AS txn30_day_threshold
 
    , 'ICA_PerCard_PerMID_TXN_Limit' AS rule_name
    FROM
        (SELECT * FROM offus_txn
        WHERE txn_date BETWEEN DATE'2025-01-01' AND  DATE'2025-01-31'
        AND isindian = 'false' AND isedcrequest = 'true')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
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11)
WHERE (txn1_min >= txn1_min_threshold) OR 
(txn1_day >= txn1_day_threshold) OR
(txn7_day >= txn7_day_threshold) OR
(txn30_day >= txn30_day_threshold)