-- RULE x CATEGORY x UPI SUBTYPE X MONTH wise rejected txn & gmv, attempted txn & gmv & rejection rate

with overall as 
(SELECT DISTINCT A.year_month
, A.category
, IF(C.upi_subtype IS NOT NULL, c.upi_subtype, IF(A.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
, A.amount
, A.txn_id
, risk_code
, action_recommended
FROM
    (SELECT DISTINCT substr(cast(dl_last_updated as varchar(30)), 1, 7) as year_month, txn_id, amount, category
    FROM switch.txn_info_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN date'2025-01-01' AND DATE('2025-03-31')
    AND DATE(created_on) BETWEEN date'2025-01-01' AND DATE('2025-03-31'))A
LEFT JOIN
    (SELECT DISTINCT txnid
    , CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) as txn_amount
    , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
    , regexp_replace(cast(json_extract(response, '$.messages.cst[0]') as varchar), '"', '') as risk_code
    , json_extract_scalar(response, '$.action_recommended') AS action_recommended
    FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN date'2025-01-01' AND DATE('2025-03-31')
    AND (lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '')) LIKE '%@paytm%'
    or lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '')) like '%@pt%'))C
on A.txn_id = C.txnid)

SELECT A.year_month, A.category, A.upi_subtype, A.risk_code, A.rej_txns, A.rej_gmv, B.attempted_txns, B.attempted_gmv FROM
    (select year_month
    , category
    , upi_subtype
    , risk_code
    , count(txn_id) as rej_txns
    , sum(amount) as rej_gmv
    from overall
    WHERE action_recommended = 'BLOCK'
    GROUP BY 1,2,3,4)A
INNER JOIN
    (SELECT year_month, category, upi_subtype
    , count(txn_id) as attempted_txns
    , sum(amount) as attempted_gmv
    FROM overall
    GROUP BY 1,2,3)B
ON A.year_month = B.year_month AND A.category = B.category AND A.upi_subtype = B.upi_subtype;