OFFUS: MONTHLY BREACHES & CHARGEBACK & FRAUD %GE

-- MONTHLY BREACHES & CASHBACK & FRAUD %GE
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 small_vpa) as breach_card_cnt
, SUM(CAST(cashback_amount AS DOUBLE)) AS cashback_amount
, SUM(CAST(fraud_amount AS DOUBLE)) AS fraud_amount
FROM
    (SELECT DISTINCT A.txn_date, A.transactionid, A.txn_amount, A.small_vpa
    , B.amount AS cashback_amount
    , C.fraud_amount as fraud_amount
    FROM
        (SELECT * FROM team_kingkong.offus_CCUPI_vpa_mid_daily_limit_breaches)A 
    LEFT JOIN
        (select * from
            (select a.*, ROW_NUMBER() OVER (PARTITION BY txn_id ORDER BY cb_level desc) as row_num from
                (select distinct txn_id, amount, cb_level
                from charge_team_data.cb_data_analytics_snapshot_v3
                where dl_last_updated >= date '2000-01-01')a)a
        where row_num = 1)B
    on B.txn_id = A.transactionid
    LEFT JOIN
        (select * from
            (select distinct pg_txn_id as txn_id, txn_amount as fraud_amount,
            substr(txn_date,1,10) as txn_date, mid,
            min(source_reporting_date) as reporting_date
            from cdp_risk_transform.fraud_master_snapshot_v3
            where source_table <> 'freshdesk.cst_case_snapshot'
            and txn_status = 'SUCCESS'
            and dl_last_updated >= date '2025-01-01'
            group by 1,2,3,4) i
        left join
            (SELECT distinct acq_id, CAST(refund_amount AS DOUBLE) / 100 AS fraud_refund
            FROM pgaws_datalake_prod.acq_refund_snapshot_v3
            WHERE dl_last_updated >= date'2025-01-01' AND refund_status = 'SUCCESS') j
        ON i.txn_id = j.acq_id)C
    ON A.transactionid = C.txn_id)
GROUP BY 1 ORDER BY 1;