ONUS Rejection Rate, Month X Rule wise breach summary, fill rate

-- MONTH x CATEGORY X RULE wise Rejected txn & gmv, Attempted txn & gmv and Rejection rate
DROP TABLE team_kingkong.onus_rej_rate_monthly;

CREATE TABLE team_kingkong.onus_rej_rate_monthly AS
WITH flattened as 
    (SELECT DISTINCT mnth, txn_date, 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
        , dateinserted as txn_date
        , paytmmerchantid
        , actionrecommended
        FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
        WHERE dl_last_updated BETWEEN date '2025-01-01' AND DATE'2025-07-08'
        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.txn_date, A.business_category, A.strategy_name, A.rej_txns, A.rej_gmv, B.attempted_txns, B.attempted_gmv FROM
    (select mnth, txn_date
    , business_category
    , strategy_name
    , count(transactionid) as rej_txns
    , sum(amt) as rej_gmv
    from flattened
    WHERE actionrecommended = 'BLOCK'
    GROUP BY 1,2,3,4)A
INNER JOIN
    (SELECT mnth, txn_date, business_category
    , count(transactionid) as attempted_txns
    , sum(amt) as attempted_gmv
    FROM flattened
    GROUP BY 1,2,3)B
ON A.mnth = B.mnth AND A.business_category = B.business_category AND A.txn_date = B.txn_date;


-- 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;


-- ONUS FILL RATE
CREATE TABLE team_kingkong.onus_fill_rate AS
WITH
-- 1. onus maquette
onus_maquette AS (
  SELECT *, count(*) OVER () AS total_rows
  FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
  WHERE dl_last_updated > DATE(CURRENT_DATE - INTERVAL '7' DAY)
),

-- 2. pplus async
pplus_async AS (
  SELECT *, count(*) OVER () AS total_rows
  FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
  WHERE dl_last_updated > DATE(CURRENT_DATE - INTERVAL '7' DAY)
)

-- Fill Rate % for onus maquette
SELECT 'cdp_risk_transform' as db_name, 'maquette_flattened_onus_snapshot_v3' as table_name, 'transactionid' as column_name,
       100.0 * COUNT_IF(transactionid IS NOT NULL AND trim(transactionid) != '') / MAX(total_rows) AS fill_rate_pct
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'eventAmount',
       100.0 * COUNT_IF(eventAmount IS NOT NULL AND trim(eventAmount) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'paytmmerchantid',
       100.0 * COUNT_IF(paytmmerchantid IS NOT NULL AND trim(paytmmerchantid) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'actionrecommended',
       100.0 * COUNT_IF(actionrecommended IS NOT NULL AND trim(actionrecommended) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'actionrecommendedrules',
       100.0 * COUNT_IF(actionrecommendedrules IS NOT NULL AND trim(actionrecommendedrules) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'SOURCE',
       100.0 * COUNT_IF(SOURCE IS NOT NULL AND trim(SOURCE) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'userid',
       100.0 * COUNT_IF(userid IS NOT NULL AND trim(userid) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'paymethod',
       100.0 * COUNT_IF(paymethod IS NOT NULL AND trim(paymethod) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'responsestatus',
       100.0 * COUNT_IF(responsestatus IS NOT NULL AND trim(responsestatus) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'velocitytimestamp',
       100.0 * COUNT_IF(velocitytimestamp IS NOT NULL AND trim(velocitytimestamp) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'eventid',
       100.0 * COUNT_IF(eventid IS NOT NULL AND trim(eventid) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'apicodeoption',
       100.0 * COUNT_IF(apicodeoption IS NOT NULL AND trim(apicodeoption) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'subscriberid',
       100.0 * COUNT_IF(subscriberid IS NOT NULL AND trim(subscriberid) != '') / MAX(total_rows)
FROM onus_maquette
UNION ALL
SELECT 'cdp_risk_transform', 'maquette_flattened_onus_snapshot_v3', 'amount',
       100.0 * COUNT_IF(amount IS NOT NULL AND trim(amount) != '') / MAX(total_rows)
FROM onus_maquette

-- Fill Rate % for pplus async
UNION ALL
SELECT 'risk_maquette_data_async', 'pplus_payment_result_prod_async_snapshot_v3', 'eventlinkid',
       100.0 * COUNT_IF(eventlinkid IS NOT NULL AND trim(eventlinkid) != '') / MAX(total_rows)
FROM pplus_async;