ONUS Rejection Rate, Month X Rule wise breach summary

-- MONTH x CATEGORY X RULE wise Rejected txn & gmv, Attempted txn & gmv and Rejection rate
CREATE TABLE team_kingkong.onus_rej_rate_monthly AS
WITH flattened as 
    (SELECT DISTINCT mnth, transactionid, strategy_name, actionrecommended, amt, case when m1.mid is not null then category else 'Others' end as business_category FROM
        (select transactionid
        , json_extract_scalar(actionrecommendedrules,'$.actionRecommendedRules[0]') as strategy_name
        , cast(eventAmount as double)/100 as amt
        , substr(cast(dateinserted as varchar), 1,7) as mnth
        -- , paymethod,eventName,addAndPay
        , paytmmerchantid
        , actionrecommended
        FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
        WHERE dl_last_updated BETWEEN date '2025-01-01' AND DATE'2025-05-31'
        AND SOURCE = 'PG') a
    left join 
        (select * from team_kingkong.voc_mid_categorization
        where mid != '') m1 
    on a.paytmmerchantid = m1.mid)
 
SELECT A.mnth, A.business_category, A.strategy_name, A.rej_txns, A.rej_gmv, B.attempted_txns, B.attempted_gmv FROM
    (select mnth
    , business_category
    , strategy_name
    , count(transactionid) as rej_txns
    , sum(amt) as rej_gmv
    from flattened
    WHERE actionrecommended = 'BLOCK'
    GROUP BY 1,2,3)A
INNER JOIN
    (SELECT mnth, business_category
    , count(transactionid) as attempted_txns
    , sum(amt) as attempted_gmv
    FROM flattened
    GROUP BY 1,2)B
ON A.mnth = B.mnth AND A.business_category = B.business_category;
 


-- Rule X Monthly Breach rate
SELECT substr(cast(dateinserted as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt
, 'CCBP_attempt_Txn_user_1d_7d_30d' AS rule_name
FROM team_kingkong.onus_CCBP_attempt_Txn_user_1d_7d_30d_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
-- , 'on_us_sbi_nb_limit' AS rule_name 
-- FROM team_kingkong.onus_on_us_sbi_nb_limit_breaches
-- GROUP BY 1

UNION

SELECT substr(cast(dateinserted as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt
, 'CCBP_sucTxn_user_1d_7d_30d' AS rule_name  
FROM team_kingkong.onus_CCBP_sucTxn_user_1d_7d_30d_breaches
GROUP BY 1

UNION

SELECT substr(cast(dateinserted as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt
, 'Fastag_TrustedUser_CCDC_Weekly_Monthly_limitCheck' AS rule_name  
FROM team_kingkong.onus_Fastag_TrustedUser_CCDC_Weekly_Monthly_limitCheck_breaches
GROUP BY 1

UNION

SELECT substr(cast(dateinserted as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt
, 'Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck' AS rule_name  
FROM team_kingkong.onus_Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck_breaches
GROUP BY 1

UNION

SELECT substr(cast(dateinserted as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt
, 'UtilityEventTxnLImit' AS rule_name  
FROM team_kingkong.onus_UtilityEventTxnLImit_breaches
GROUP BY 1

UNION

SELECT substr(cast(dateinserted as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt
, 'on_us_loan_repayments_user_limits' AS rule_name  
FROM team_kingkong.onus_on_us_loan_repayments_user_limits_breaches
GROUP BY 1

UNION

SELECT substr(cast(dateinserted as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt
, 'Fastag_NonTrustedUser_CCDC_Monthly_limitCheck' AS rule_name  
FROM team_kingkong.onus_Fastag_NonTrustedUser_CCDC_Monthly_limitCheck_breaches
GROUP BY 1

UNION

SELECT substr(cast(dateinserted as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt
, 'CCBP_GMV_per_user_1d_7d_30d' AS rule_name  
FROM team_kingkong.onus_CCBP_GMV_per_user_1d_7d_30d_breaches
GROUP BY 1;

-- ONUS OVERALL BREACH RATE
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month, COUNT(transactionid) as breach_cnt, SUM(amt) as breach_amt FROM
(SELECT DATE(dateinserted) AS txn_date, transactionid, amt
FROM team_kingkong.onus_CCBP_attempt_Txn_user_1d_7d_30d_breaches

-- UNION

-- SELECT DATE(dateinserted) AS txn_date, transactionid, amt
-- FROM team_kingkong.onus_on_us_sbi_nb_limit_breaches

UNION

SELECT DATE(dateinserted) AS txn_date, transactionid, amt 
FROM team_kingkong.onus_CCBP_sucTxn_user_1d_7d_30d_breaches

UNION

SELECT DATE(dateinserted) AS txn_date, transactionid, amt
FROM team_kingkong.onus_Fastag_TrustedUser_CCDC_Weekly_Monthly_limitCheck_breaches

UNION

SELECT DATE(dateinserted) AS txn_date, transactionid, amt
FROM team_kingkong.onus_Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck_breaches

UNION

SELECT DATE(dateinserted) AS txn_date, transactionid, amt
FROM team_kingkong.onus_UtilityEventTxnLImit_breaches

UNION

SELECT DATE(dateinserted) AS txn_date, transactionid, amt
FROM team_kingkong.onus_on_us_loan_repayments_user_limits_breaches

UNION

SELECT DATE(dateinserted) AS txn_date, transactionid, amt
FROM team_kingkong.onus_Fastag_NonTrustedUser_CCDC_Monthly_limitCheck_breaches

UNION

SELECT DATE(dateinserted) AS txn_date, transactionid, amt
FROM team_kingkong.onus_CCBP_GMV_per_user_1d_7d_30d_breaches
)
GROUP BY 1;