ONUS Rejection Rate

-- MONTH x CATEGORY X RULE wise Rejected txn & gmv, Attempted txn & gmv and Rejection rate
WITH flattened as 
    (SELECT DISTINCT mnth, transactionid, strategy_name, actionrecommended, amt, case when m1.mid is not null then category else 'Others' end as business_category FROM
        (select transactionid
        , json_extract_scalar(actionrecommendedrules,'$.actionRecommendedRules[0]') as strategy_name
        , cast(eventAmount as double)/100 as amt
        , substr(cast(dateinserted as varchar), 1,7) as mnth
        -- , paymethod,eventName,addAndPay
        , paytmmerchantid
        , actionrecommended
        FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
        WHERE dl_last_updated BETWEEN date '2025-01-01' AND DATE'2025-04-30'
        AND SOURCE = 'PG') a
    left join 
        (select * from team_kingkong.voc_mid_categorization
        where mid != '') m1 
    on a.paytmmerchantid = m1.mid)

SELECT A.mnth, A.business_category, A.strategy_name, A.rej_txns, A.rej_gmv, B.attempted_txns, B.attempted_gmv FROM
    (select mnth
    , business_category
    , strategy_name
    , count(transactionid) as rej_txns
    , sum(amt) as rej_gmv
    from flattened
    WHERE actionrecommended = 'BLOCK'
    GROUP BY 1,2,3)A
INNER JOIN
    (SELECT mnth, business_category
    , count(transactionid) as attempted_txns
    , sum(amt) as attempted_gmv
    FROM flattened
    GROUP BY 1,2)B
ON A.mnth = B.mnth AND A.business_category = B.business_category;