New
Mon Jul 03 2023 08:54:58 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%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
Comments