New

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

-- Overall Phonepe
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
, 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
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
    from fraud.pod_metrics_master
    WHERE txn_year = 2023 AND txn_mnth = 6 
    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
    from fraud.pod_metrics_master
    WHERE reported_year = 2023 AND reported_month = 6 
    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

-- Purchase of EGV using any instrument
-- Final - 22June2023

SELECT A.txn_year
, A.txn_mnth
, A.txn_mnth_egv_purchase_Success_Cnt
, A.txn_mnth_egv_purchase_Success_Amt_INR
, AA.txn_mnth_egv_purchase_Fraud_Cnt		
, AA.txn_mnth_egv_purchase_Fraud_Amt_INR
, ''
, A.txn_year
, A.txn_mnth
, B.txn_mnth_egv_redeem_Success_Cnt
, B.txn_mnth_egv_redeem_Success_Amt_INR
, BB.txn_mnth_egv_redeem_Fraud_Cnt		
, BB.txn_mnth_egv_redeem_Fraud_Amt_INR
FROM
    (SELECT txn_year
    , txn_mnth
    , SUM(NVL(successful_txn, 0)) AS txn_mnth_egv_purchase_Success_Cnt
    , SUM(NVL(successful_amount, 0)) AS txn_mnth_egv_purchase_Success_Amt_INR
    from fraud.pod_metrics_master
    WHERE categories = 'B08: PhonePe EGV' 
    AND txn_year = 2023 AND txn_mnth >= 4
    GROUP BY txn_year, txn_mnth)A
INNER JOIN 
    (SELECT reported_year
    , reported_month
    , SUM(NVL(fraud_cnt, 0)) AS txn_mnth_egv_purchase_Fraud_Cnt		
    , SUM(NVL(fraud_amt, 0)) AS txn_mnth_egv_purchase_Fraud_Amt_INR
    from fraud.pod_metrics_master
    WHERE categories = 'B08: PhonePe EGV' 
    AND reported_year = 2023 AND reported_month >= 4
    GROUP BY reported_year, reported_month)AA
ON A.txn_year = AA.reported_year AND A.txn_mnth = AA.reported_month
INNER JOIN
    (SELECT txn_year
    , txn_mnth
    , SUM(NVL(successful_txn, 0)) AS txn_mnth_egv_redeem_Success_Cnt
    , SUM(NVL(successful_amount, 0)) AS txn_mnth_egv_redeem_Success_Amt_INR
    FROM fraud.pod_metrics_master 
    WHERE txn_year = 2023 AND txn_mnth >= 4
    AND instrument_type = 'A4: EGV'
    GROUP BY txn_year, txn_mnth)B
ON A.txn_year = B.txn_year AND A.txn_mnth = B.txn_mnth
INNER JOIN
    (SELECT reported_year
    , reported_month
    , SUM(NVL(fraud_cnt, 0)) AS txn_mnth_egv_redeem_Fraud_Cnt		
    , SUM(NVL(fraud_amt, 0)) AS txn_mnth_egv_redeem_Fraud_Amt_INR
    FROM fraud.pod_metrics_master 
    WHERE reported_year = 2023 AND reported_month >= 4
    AND instrument_type = 'A4: EGV'
    GROUP BY reported_year, reported_month)BB
ON A.txn_year = BB.reported_year AND A.txn_mnth = BB.reported_month


-- PPI 
%jdbc(hive)
set tez.queue.name=merchant;
set hive.execution.engine=tez;

SELECT A.txn_year
, A.txn_mnth
, A.txn_mnth_egv_purchase_Success_Cnt
, A.txn_mnth_egv_purchase_Success_Amt_INR
, AA.txn_mnth_egv_purchase_Fraud_Cnt		
, AA.txn_mnth_egv_purchase_Fraud_Amt_INR
, ''
, A.txn_year
, A.txn_mnth
, B.txn_mnth_egv_redeem_Success_Cnt
, B.txn_mnth_egv_redeem_Success_Amt_INR
, BB.txn_mnth_egv_redeem_Fraud_Cnt		
, BB.txn_mnth_egv_redeem_Fraud_Amt_INR
FROM
    (SELECT txn_year
    , txn_mnth
    , SUM(NVL(successful_txn, 0)) AS txn_mnth_egv_purchase_Success_Cnt
    , SUM(NVL(successful_amount, 0)) AS txn_mnth_egv_purchase_Success_Amt_INR
    from fraud.pod_metrics_master
    WHERE categories IN ('B08: PhonePe EGV', 'B09: Wallet TopUp', 'B10: Wallet top workflow') 
    AND txn_year >= 2022 AND txn_mnth >= 1
    GROUP BY txn_year, txn_mnth)A
INNER JOIN 
    (SELECT reported_year
    , reported_month
    , SUM(NVL(fraud_cnt, 0)) AS txn_mnth_egv_purchase_Fraud_Cnt		
    , SUM(NVL(fraud_amt, 0)) AS txn_mnth_egv_purchase_Fraud_Amt_INR
    from fraud.pod_metrics_master
    WHERE categories IN ('B08: PhonePe EGV', 'B09: Wallet TopUp', 'B10: Wallet top workflow') 
    AND reported_year >= 2022 AND reported_month >= 1
    GROUP BY reported_year, reported_month)AA
ON A.txn_year = AA.reported_year AND A.txn_mnth = AA.reported_month
INNER JOIN
    (SELECT txn_year
    , txn_mnth
    , SUM(NVL(successful_txn, 0)) AS txn_mnth_egv_redeem_Success_Cnt
    , SUM(NVL(successful_amount, 0)) AS txn_mnth_egv_redeem_Success_Amt_INR
    FROM fraud.pod_metrics_master 
    WHERE txn_year >= 2022 AND txn_mnth >= 1
    AND instrument_type IN ('A4: EGV', 'A5: Wallet')
    GROUP BY txn_year, txn_mnth)B
ON A.txn_year = B.txn_year AND A.txn_mnth = B.txn_mnth
INNER JOIN
    (SELECT reported_year
    , reported_month
    , SUM(NVL(fraud_cnt, 0)) AS txn_mnth_egv_redeem_Fraud_Cnt		
    , SUM(NVL(fraud_amt, 0)) AS txn_mnth_egv_redeem_Fraud_Amt_INR
    FROM fraud.pod_metrics_master 
    WHERE reported_year >= 2022 AND reported_month >= 1
    AND instrument_type IN ('A4: EGV', 'A5: Wallet')
    GROUP BY reported_year, reported_month)BB
ON A.txn_year = BB.reported_year AND A.txn_mnth = BB.reported_month