ONUS : CCBP_attempt_Txn_user_1d_7d_30d

-- CCBP_attempt_Txn_user_1d_7d_30d
DROP TABLE team_kingkong.onus_CCBP_attempt_Txn_user_1d_7d_30d_breaches;

-- CREATE TABLE team_kingkong.onus_CCBP_attempt_Txn_user_1d_7d_30d_breaches AS
INSERT INTO team_kingkong.onus_CCBP_attempt_Txn_user_1d_7d_30d_breaches
with onus_txn_base as
    (SELECT DISTINCT A.*, case when m1.mid is not null then category else 'Others' end as business_category, B.payresult FROM 
        (select distinct userid, transactionid as txn_id,
        cast(eventAmount as double) / 100 as txn_amount,
        dateinserted as txn_date,
        substr(cast(dateinserted as varchar(30)), 1, 7) as yearMonth,
        paymethod, paytmmerchantid, responsestatus, actionrecommended, velocitytimestamp,eventid
        FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
        WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'  -- 4, 5, 6, 7 done
        AND SOURCE = 'PG'
        AND paytmmerchantid IN ('PTMCBP84799392178473','PTMVIS48435535949128','PTMCBP11428987150800')) a
    INNER JOIN
        (SELECT eventlinkid, payresult 
        FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
        WHERE dl_last_updated BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31')B
    ON a.eventid = B.eventlinkid
    left join
        (select * from team_kingkong.voc_mid_categorization where mid != '') m1
    on a.paytmmerchantid = m1.mid)

SELECT *,CASE
  WHEN txn_attempted_same_day >= txn_attempted_same_day_threshold
    AND txn_attempted_7_day >= txn_attempted_7_day_threshold
    AND txn_attempted_cal_month >= txn_attempted_cal_month_threshold THEN 'same_day, 7_day & cal_month breach'
  WHEN txn_attempted_same_day >= txn_attempted_same_day_threshold
    AND txn_attempted_7_day >= txn_attempted_7_day_threshold THEN 'same_day & 7_day breach'
  WHEN txn_attempted_same_day >= txn_attempted_same_day_threshold
    AND txn_attempted_cal_month >= txn_attempted_cal_month_threshold THEN 'same_day & cal_month breach'
  WHEN txn_attempted_7_day >= txn_attempted_7_day_threshold
    AND txn_attempted_cal_month >= txn_attempted_cal_month_threshold THEN '7_day & cal_month breach'
  WHEN txn_attempted_same_day >= txn_attempted_same_day_threshold THEN 'same_day breach'
  WHEN txn_attempted_7_day >= txn_attempted_7_day_threshold THEN '7_day breach'
  WHEN txn_attempted_cal_month >= txn_attempted_cal_month_threshold THEN 'cal_month breach'
  ELSE NULL END AS breach_reason FROM 
    (SELECT A.*
    -- No.of attempted txns per user per calendar day  > 12 (consider only the CCBP transactions)
    , COUNT(IF(DATE(B.txn_date) = DATE(A.txn_date), B.txn_id, NULL)) AS txn_attempted_same_day
    , 12 AS txn_attempted_same_day_threshold
    -- No.of attempted txns last 7 days > 20 (consider only the CCBP transactions)
    , COUNT(IF(DATE(B.txn_date) BETWEEN DATE(DATE(A.txn_date) - INTERVAL '7' DAY) AND DATE(A.txn_date), B.txn_id, NULL)) AS txn_attempted_7_day
    , 20 AS txn_attempted_7_day_threshold
    -- No.of attempted txns per calendar month > 30 (consider only the CCBP transactions)
    , COUNT(IF(DATE(B.txn_date) BETWEEN date_trunc('month', DATE(A.txn_date)) AND DATE(A.txn_date), B.txn_id, NULL)) AS txn_attempted_cal_month
    , 30 AS txn_attempted_cal_month_threshold
    FROM
        (SELECT * FROM onus_txn_base
        WHERE DATE(txn_date) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
        AND responsestatus = 'SUCCESS' AND actionrecommended = 'PASS'
        AND payresult = 'payment_success')A
    INNER JOIN
        (SELECT * FROM onus_txn_base)B
    ON A.userid = B.userid AND A.txn_id <> B.txn_id AND B.velocitytimestamp < A.velocitytimestamp
    AND DATE(B.txn_date) BETWEEN DATE(A.txn_date - INTERVAL '30' DAY) AND DATE(A.txn_date)
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13)
WHERE (txn_attempted_same_day >= txn_attempted_same_day_threshold) 
OR (txn_attempted_7_day >= txn_attempted_7_day_threshold) 
OR (txn_attempted_cal_month >= txn_attempted_cal_month_threshold);




-- onus_UtilityEventTxnLImit_breaches
-- UtilityEventTxnLImit
-- ONUS : UtilityEventTxnLImit
DROP TABLE team_kingkong.onus_UtilityEventTxnLImit_breaches;

-- CREATE TABLE team_kingkong.onus_UtilityEventTxnLImit_breaches AS 
INSERT INTO team_kingkong.onus_UtilityEventTxnLImit_breaches 
SELECT A.*, case when m1.mid is not null then category else 'Others' end as business_category
, 1000001 as per_txn_threshold
, 'per txn threshold breached' as breach_reason
FROM 
    (select distinct userid, transactionid,
    cast(eventAmount as double) / 100 as amt,
    dateinserted,
    substr(cast(dateinserted as varchar(30)), 1, 7) as mnth,
    paymethod, paytmmerchantid, responsestatus, actionrecommended
    FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
    AND SOURCE = 'PG' AND paymethod IN ('DEBIT_CARD', 'CREDIT_CARD')
AND responsestatus = 'SUCCESS' AND actionrecommended <> 'BLOCK'
    AND (cast(eventAmount as double) / 100) > 1000001
    AND eventid IN (SELECT eventlinkid
    FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
    WHERE dl_last_updated BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
    AND payresult = 'payment_success')) a
left join
    (select * from team_kingkong.voc_mid_categorization where mid != '') m1
on a.paytmmerchantid = m1.mid;

ALTER TABLE team_kingkong.onus_UtilityEventTxnLImit_breaches
ADD COLUMN breach_reason VARCHAR(30);


SELECT mid_type, yearmonth, SUM(rejected_gmv)/ AVG(attempted_gmv) FROM team_kingkong.offus_rej_rate_monthly
GROUP BY 1,2;

DESC team_kingkong.tpap_rej_rate_monthly;

SELECT year_month, category, SUM(rej_gmv) AS rejected_gmv, AVG(attempted_gmv) AS avg_attempted_gmv
, sum(attempted_gmv)
, SUM(rej_gmv)/ AVG(attempted_gmv) AS rej_rate
FROM
    (SELECT year_month, risk_code, rej_gmv, attempted_gmv
    FROM team_kingkong.tpap_rej_rate_monthly)A
INNER JOIN
    (SELECT risk_code, category
    FROM team_kingkong.tpapRulesforDB)B
ON A.risk_code = B.risk_code
GROUP BY 1,2;