Overall

%jdbc(hive)
set tez.queue.name=phonepe_verified;
set hive.execution.engine=tez;

-- Overall (C2C & C2M Txn considered)	
SELECT A.txn_year
, A.txn_mnth
, A.txn_mnth_Success_Cnt
, A.txn_mnth_Success_Amt_INR
, A.txn_mnth_Fraud_Cnt		
, A.txn_mnth_Fraud_Amt_INR
, A.txn_mnth_Amt_fraud_perc
, B.Rep_mnth_Fraud_Cnt		
, B.Rep_mnth_Fraud_Amt_INR
, B.Rep_mnth_Fraud_Amt_INR / A.txn_mnth_Success_Amt_INR AS Rep_mnth_fraud_amt_perc
FROM
    (SELECT txn_year
    , txn_mnth
    , SUM(NVL(successful_txn, 0)) AS txn_mnth_Success_Cnt
    , SUM(NVL(successful_amount, 0)) AS txn_mnth_Success_Amt_INR
    , SUM(NVL(fraud_cnt, 0)) AS txn_mnth_Fraud_Cnt		
    , SUM(NVL(fraud_amt, 0)) AS txn_mnth_Fraud_Amt_INR
    , SUM(NVL(fraud_amt, 0)) / SUM(successful_amount) AS txn_mnth_Amt_fraud_perc
    from fraud.pod_metrics_master
    WHERE categories IN ('A1: C2C', 'A2: C2E Consumer', 'A5: C2A', 'B01: Billpay', 'B02: Edu payment', 'B03: House Rent', 'B04: Card Payment', 'B05: Ext Brand Voucher', 'B06: Recharge', 'B07: Google Voucher', 'B08: PhonePe EGV', 'B09: Wallet TopUp', 'B10: Wallet top workflow', 'C01: Offline MX', 'C02: Offline Aggregator', 'C03: P2PM', 'C04: P2ML', 'C05: InApp MX', 'C06: Online MX', 'C07: Online Aggregator', 'C08: B2B_PG', 'C09: C2E Merchant', 'C19: ONDC', 'D1: Insurance', 'D2: Mutual Fund', 'D3: Gold Buy', 'Z: Others')
    GROUP BY txn_year, txn_mnth)A
LEFT JOIN
    (SELECT reported_year
    , reported_month
    , SUM(NVL(fraud_cnt, 0)) AS Rep_mnth_Fraud_Cnt		
    , SUM(NVL(fraud_amt, 0)) AS Rep_mnth_Fraud_Amt_INR
    from fraud.pod_metrics_master
    WHERE categories IN ('A1: C2C', 'A2: C2E Consumer', 'A5: C2A', 'B01: Billpay', 'B02: Edu payment', 'B03: House Rent', 'B04: Card Payment', 'B05: Ext Brand Voucher', 'B06: Recharge', 'B07: Google Voucher', 'B08: PhonePe EGV', 'B09: Wallet TopUp', 'B10: Wallet top workflow', 'C01: Offline MX', 'C02: Offline Aggregator', 'C03: P2PM', 'C04: P2ML', 'C05: InApp MX', 'C06: Online MX', 'C07: Online Aggregator', 'C08: B2B_PG', 'C09: C2E Merchant', 'C19: ONDC', 'D1: Insurance', 'D2: Mutual Fund', 'D3: Gold Buy', 'Z: Others')
    GROUP BY reported_year, reported_month)B
ON A.txn_year = B.reported_year AND A.txn_mnth = B.reported_month
ORDER BY A.txn_year, A.txn_mnth