Monthly breach count for all rules

-- MONTHLY BREACH COUNT
-- team_kingkong.offus_MID_CCDC_Daily_TXN_limit_Check_breaches
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month
, COUNT(transactionid) AS breach_cnt
, SUM(txn_amount) as breach_amt
, COUNT(DISTINCT globalcardindex) as card_cnt
FROM
(SELECT DISTINCT *
FROM team_kingkong.offus_MID_CCDC_Daily_TXN_limit_Check_breaches)
GROUP BY 1 ORDER BY 1;

-- team_kingkong.offus_edc_card_velocity_count_breaches
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month
, COUNT(transactionid) AS breach_cnt
, SUM(txn_amount) as breach_amt
, COUNT(DISTINCT globalcardindex) as card_cnt
FROM
(SELECT DISTINCT *
FROM team_kingkong.offus_edc_card_velocity_count_breaches)
GROUP BY 1 ORDER BY 1;

-- team_kingkong.offus_Merchant_PerTxnLimit_Check_breaches
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month
, COUNT(transactionid) AS breach_cnt
, SUM(txn_amount) as breach_amt
-- , COUNT(DISTINCT globalcardindex) as card_cnt
FROM
(SELECT DISTINCT *
FROM team_kingkong.offus_Merchant_PerTxnLimit_Check_breaches)
GROUP BY 1 ORDER BY 1;

-- team_kingkong.offus_edc_card_velocity_amount_breaches
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month
, COUNT(transactionid) AS breach_cnt
, SUM(txn_amount) as breach_amt
, COUNT(DISTINCT globalcardindex) as card_cnt
FROM
(SELECT DISTINCT *
FROM team_kingkong.offus_edc_card_velocity_amount_breaches)
GROUP BY 1 ORDER BY 1;

-- team_kingkong.offus_MID_UPI_Daily_TXN_limit_Check_breaches
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month
, COUNT(transactionid) AS breach_cnt
, SUM(txn_amount) as breach_amt
, COUNT(DISTINCT globalcardindex) as card_cnt
FROM
(SELECT DISTINCT *
FROM team_kingkong.offus_MID_UPI_Daily_TXN_limit_Check_breaches)
GROUP BY 1 ORDER BY 1;

-- team_kingkong.offus_oil_gas_dc_limit_EDC_breaches
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month
, COUNT(transactionid) AS breach_cnt
, SUM(txn_amount) as breach_amt
, COUNT(DISTINCT globalcardindex) as card_cnt
FROM
(SELECT DISTINCT *
FROM team_kingkong.offus_oil_gas_dc_limit_EDC_breaches)
GROUP BY 1 ORDER BY 1;

-- team_kingkong.offus_ICA_Unsafe_Country_Transactions_breaches
SELECT substr(cast(txn_date as varchar(30)), 1, 7) as year_month
, COUNT(transactionid) AS breach_cnt
, SUM(txn_amount) as breach_amt
, COUNT(DISTINCT globalcardindex) as card_cnt
FROM
(SELECT DISTINCT *
FROM team_kingkong.offus_ICA_Unsafe_Country_Transactions_breaches)
GROUP BY 1 ORDER BY 1;