Success & Fraud Numbers

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

SELECT A.txn_year
, A.txn_mnth
, A.txn_mnth_Overall_Success_Cnt
, A.txn_mnth_Overall_Success_Amt_INR
, A.txn_mnth_Overall_Fraud_Cnt		
, A.txn_mnth_Overall_Fraud_Amt_INR
, A.txn_mnth_Overall_Amt_fraud_perc
, A.txn_mnth_CC_DC_Success_Cnt
, A.txn_mnth_CC_DC_Success_Amt_INR
, A.txn_mnth_CC_DC_Fraud_Cnt		
, A.txn_mnth_CC_DC_Fraud_Amt_INR
, A.txn_mnth_CC_DC_Amt_fraud_perc
, B.Rep_mnth_Overall_Fraud_Cnt		
, B.Rep_mnth_Overall_Fraud_Amt_INR
, B.Rep_mnth_Overall_Fraud_Amt_INR / A.txn_mnth_Overall_Success_Amt_INR AS Rep_mnth_fraud_overall_perc
, B.Rep_mnth_CC_DC_Fraud_Cnt		
, B.Rep_mnth_CC_DC_Fraud_Amt_INR
, B.Rep_mnth_CC_DC_Fraud_Amt_INR / A.txn_mnth_CC_DC_Success_Amt_INR AS Rep_mnth_fraud_CC_DC_perc
FROM
    (SELECT txn_year
    , txn_mnth
    , SUM(NVL(successful_txn, 0)) AS txn_mnth_Overall_Success_Cnt
    , SUM(NVL(successful_amount, 0)) AS txn_mnth_Overall_Success_Amt_INR
    , SUM(NVL(fraud_cnt, 0)) AS txn_mnth_Overall_Fraud_Cnt		
    , SUM(NVL(fraud_amt, 0)) AS txn_mnth_Overall_Fraud_Amt_INR
    , SUM(NVL(fraud_amt, 0)) / SUM(successful_amount) AS txn_mnth_Overall_Amt_fraud_perc
    
    , SUM(CASE WHEN instrument_type IN ('A2: Credit Card', 'A3: Debit Card') THEN NVL(successful_txn, 0) ELSE 0 END) AS txn_mnth_CC_DC_Success_Cnt
    , SUM(CASE WHEN instrument_type IN ('A2: Credit Card', 'A3: Debit Card') THEN NVL(successful_amount, 0) ELSE 0 END) AS txn_mnth_CC_DC_Success_Amt_INR
    , SUM(CASE WHEN instrument_type IN ('A2: Credit Card', 'A3: Debit Card') THEN NVL(fraud_cnt, 0) ELSE 0 END) AS txn_mnth_CC_DC_Fraud_Cnt		
    , SUM(CASE WHEN instrument_type IN ('A2: Credit Card', 'A3: Debit Card') THEN NVL(fraud_amt, 0) ELSE 0 END) AS txn_mnth_CC_DC_Fraud_Amt_INR
    , SUM(CASE WHEN instrument_type IN ('A2: Credit Card', 'A3: Debit Card') THEN NVL(fraud_amt, 0) ELSE 0 END) / 
            SUM(CASE WHEN instrument_type IN ('A2: Credit Card', 'A3: Debit Card') THEN successful_amount ELSE 0 END) AS txn_mnth_CC_DC_Amt_fraud_perc
            
    from fraud.pod_metrics_master
    WHERE categories = 'B08: PhonePe EGV' 
    -- AND txn_year = 2023 AND txn_mnth = 5 AND 
    GROUP BY txn_year, txn_mnth)A
LEFT JOIN
    (SELECT reported_year
    , reported_month
    , SUM(NVL(fraud_cnt, 0)) AS Rep_mnth_Overall_Fraud_Cnt		
    , SUM(NVL(fraud_amt, 0)) AS Rep_mnth_Overall_Fraud_Amt_INR
    , SUM(CASE WHEN instrument_type IN ('A2: Credit Card', 'A3: Debit Card') THEN NVL(fraud_cnt, 0) ELSE 0 END) AS Rep_mnth_CC_DC_Fraud_Cnt		
    , SUM(CASE WHEN instrument_type IN ('A2: Credit Card', 'A3: Debit Card') THEN NVL(fraud_amt, 0) ELSE 0 END) AS Rep_mnth_CC_DC_Fraud_Amt_INR
    from fraud.pod_metrics_master
    WHERE categories = 'B08: PhonePe EGV' 
    -- AND reported_year = 2023 AND reported_month = 5 AND 
    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