OFFUS Rejection Rate, Month X Rule wise breaches

-- REJECTION RATE 
CREATE TABLE team_kingkong.offus_rej_rate_monthly AS
WITH offus_base as 
(select DISTINCT a.*, case when edc_mid is not null then 'EDC' else 'QR' end as mid_type from
    (SELECT DISTINCT pg_mid from cdo.total_offline_merchant_base_snapshot_v3) f
INNER join
    (select distinct actionrecommended
    , json_extract_scalar(actionrecommendedrulestatus, '$[0].status') as rule_status
    , json_extract_scalar(actionrecommendedrulestatus, '$[0].versionedRule.ruleName') as rule_name
    , transactionid
    , cast(eventamount as double)/100 as txn_amount
    , paytmmerchantid
    , substr(cast(dl_last_updated as varchar(30)), 1, 7) AS yearMonth
    from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    where dl_last_updated BETWEEN date '2025-01-01' AND DATE'2025-05-31'
    and paymethod in ('UPI','CREDIT_CARD','DEBIT_CARD','EMI','EMI_DC')) 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)

SELECT A.*, B.attempted_txn, B.attempted_gmv FROM
    (SELECT yearMonth, rule_name, mid_type
    , COUNT(transactionid) as rejected_txn
    , SUM(txn_amount) as rejected_gmv
    FROM offus_base
    WHERE actionrecommended = 'BLOCK' AND rule_status = 'LIVE'
    GROUP BY 1,2,3)A
INNER JOIN
    (SELECT yearMonth, mid_type
    , COUNT(transactionid) as attempted_txn
    , SUM(txn_amount) as attempted_gmv
    FROM offus_base
    GROUP BY 1,2)B
ON A.yearMonth = B.yearMonth AND A.mid_type = B.mid_type

-- Rule X Monthly Breach rate
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'MID_CCDC_Daily_TXN_limit_Check' AS rule_name
FROM team_kingkong.offus_MID_CCDC_Daily_TXN_limit_Check_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'edc_card_velocity_count' AS rule_name 
FROM team_kingkong.offus_edc_card_velocity_count_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'Merchant_PerTxnLimit_Check' AS rule_name  
FROM team_kingkong.offus_Merchant_PerTxnLimit_Check_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'edc_card_velocity_amount' AS rule_name  
FROM team_kingkong.offus_edc_card_velocity_amount_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'MID_UPI_Daily_TXN_limit_Check' AS rule_name  
FROM team_kingkong.offus_MID_UPI_Daily_TXN_limit_Check_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'oil_gas_dc_limit_EDC' AS rule_name  
FROM team_kingkong.offus_oil_gas_dc_limit_EDC_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'ICA_Unsafe_Country_Transactions' AS rule_name  
FROM team_kingkong.offus_ICA_Unsafe_Country_Transactions_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'CCUPI_vpa_mid_hourly_limit' AS rule_name  
FROM team_kingkong.offus_CCUPI_vpa_mid_hourly_limit_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'ICA_PerCard_PerMID_TXN_Limit' AS rule_name  
FROM team_kingkong.offus_ICA_PerCard_PerMID_TXN_Limit_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'CCUPI_vpa_mid_daily_limit' AS rule_name  
FROM team_kingkong.offus_CCUPI_vpa_mid_daily_limit_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'ICA_OddTime_PerCard_PerMID_EDC' AS rule_name  
FROM team_kingkong.offus_ICA_OddTime_PerCard_PerMID_EDC_breaches
GROUP BY 1

UNION

SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt
, 'ICA_Bank_Decline_Threshold_Block' AS rule_name  
FROM team_kingkong.offus_ICA_Bank_Decline_Threshold_Block_breaches
GROUP BY 1;

-- OFFUS OVERALL BREACH RATE
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(txn_amount) as breach_amt FROM
(SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_MID_CCDC_Daily_TXN_limit_Check_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_edc_card_velocity_count_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount 
FROM team_kingkong.offus_Merchant_PerTxnLimit_Check_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_edc_card_velocity_amount_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_MID_UPI_Daily_TXN_limit_Check_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_oil_gas_dc_limit_EDC_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_ICA_Unsafe_Country_Transactions_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_CCUPI_vpa_mid_hourly_limit_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_ICA_PerCard_PerMID_TXN_Limit_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount 
FROM team_kingkong.offus_CCUPI_vpa_mid_daily_limit_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_ICA_OddTime_PerCard_PerMID_EDC_breaches

UNION

SELECT DATE(txn_date) AS txn_date, transactionid, txn_amount
FROM team_kingkong.offus_ICA_Bank_Decline_Threshold_Block_breaches)
GROUP BY 1;