DROP TABLE team_kingkong.offus_rej_rate_monthly2;

-- CREATE TABLE team_kingkong.offus_rej_rate_monthly2 AS
INSERT INTO team_kingkong.offus_rej_rate_monthly2 -- JUNE pending
WITH offus_base AS 
    (SELECT DISTINCT a.*, CASE WHEN NOT edc_mid IS 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
        , IF(transactionid IS NOT NULL, transactionid, eventname) AS transactionid
        , CAST(IF(eventamount = '', '0', eventamount) AS DOUBLE) / 100 AS txn_amount
        , paytmmerchantid, SUBSTR(CAST(dl_last_updated AS VARCHAR(30)), 1, 7) AS yearMonth
        , DATE(dl_last_updated) AS txn_date
        FROM cdp_risk_transform.maquette_flattened_offus_snapshot_v3
        WHERE dl_last_updated BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
        AND DATE(dateinserted) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
        AND json_extract_scalar(actionrecommendedrulestatus, '$[0].status') = 'LIVE')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 >= CAST('2010-01-01' AS DATE))b 
    ON a.paytmmerchantid = b.edc_mid)
 
SELECT A.*,
B.attempted_txn,
B.attempted_gmv
FROM
    (SELECT txn_date, yearMonth, rule_name, mid_type,
    COUNT(transactionid) AS rejected_txn,
    SUM(txn_amount) AS rejected_gmv
    FROM offus_base
    WHERE actionrecommended = 'BLOCK'
    GROUP BY 1, 2, 3, 4)A
INNER JOIN 
    (SELECT txn_date, yearMonth, mid_type,
    COUNT(transactionid) AS attempted_txn,
    SUM(txn_amount) AS attempted_gmv
    FROM offus_base
    GROUP BY 1, 2, 3)B 
ON A.yearMonth = B.yearMonth
AND A.mid_type = B.mid_type
AND A.txn_date = B.txn_date;


DROP TABLE team_kingkong.offus_rej_rate_monthly ;
 
-- CREATE TABLE team_kingkong.offus_rej_rate_monthly AS
INSERT INTO team_kingkong.offus_rej_rate_monthly
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(IF(eventamount = '', '0', eventamount) as double)/100 as txn_amount
    , paytmmerchantid
    , substr(cast(dl_last_updated as varchar(30)), 1, 7) AS yearMonth
    , date(dl_last_updated) as txn_date
    from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    where dl_last_updated BETWEEN date '2025-07-31' AND DATE'2025-08-03'
    AND DATE(dateinserted) BETWEEN date '2025-07-31' AND DATE'2025-08-03') 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 txn_date, 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,4)A
INNER JOIN
    (SELECT txn_date, yearMonth, mid_type
    , COUNT(transactionid) as attempted_txn
    , SUM(txn_amount) as attempted_gmv
    FROM offus_base
    GROUP BY 1,2,3)B
ON A.yearMonth = B.yearMonth AND A.mid_type = B.mid_type AND A.txn_date = B.txn_date;


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