OFFUS Rejection Rate

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(eventamount as double)/100 as txn_amount
    , paytmmerchantid
    , substr(cast(dl_last_updated as varchar(30)), 1, 7) AS yearMonth
    from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    where dl_last_updated BETWEEN date '2025-01-01' AND DATE'2025-03-31'
    and paymethod in ('UPI','CREDIT_CARD','DEBIT_CARD','EMI','EMI_DC')) 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 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)A
INNER JOIN
    (SELECT yearMonth, mid_type
    , COUNT(transactionid) as attempted_txn
    , SUM(txn_amount) as attempted_gmv
    FROM offus_base
    GROUP BY 1,2)B
ON A.yearMonth = B.yearMonth AND A.mid_type = B.mid_type