EGV report monthly: CORRECT

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

-- EGV PURCHASE SUCCESS BASED ON TXN MONTH
select year, month, COUNT(merchant_transaction_id) AS cnt, SUM(original_balance/100) AS amt
from egv.gift_cards 
where year = 2024 AND month = prevMonthNo
AND program_id = 'PHONEPEGC' AND tenant_id LIKE 'PHONEPE%' AND merchant_id = 'PHONEPEGC'
GROUP BY year, month
 
-- WALLET TOPUP SUCCESS BASED ON TXN MONTH
SELECT year, month, COUNT(merchant_reference_id) AS cnt, SUM(amount/100) as amt
FROM wallet.transaction_master
WHERE year = 2024 AND month = prevMonthNo 
AND category = 'TOPUP' AND txn_type = 'CREDIT'
AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
GROUP BY year, month
 
-- EGV PURCHASE FRAUD CNT & AMT BASED ON TXN MONTH
select A.year, A.month, count(eventdata_transactionid) as count, sum(amt) as amount from 
    (select merchant_transaction_id, card_number, original_balance/100 AS amt, year, month
    from egv.gift_cards 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND program_id = 'PHONEPEGC' AND tenant_id LIKE 'PHONEPE%' AND merchant_id = 'PHONEPEGC')A
INNER JOIN
    (select transaction_id, global_payment_id, amount
    from payment.transactions 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    and state='COMPLETED' and error_code='SUCCESS' and backend_error_code='SUCCESS'
    AND flow IN ('CONSUMER_TO_MERCHANT_V2', 'CONSUMER_TO_MERCHANT'))B
on A.merchant_transaction_id = B.global_payment_id
INNER JOIN
    (select eventdata_transactionid, year, month
    from foxtrot_stream.cerebro_default 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND (eventdata_fraudtxn = 1 or eventdata_action = 'FRAUD')
    and eventtype = 'MARK_TRANSACTION'
    GROUP BY eventdata_transactionid, year, month)C
on B.transaction_id = C.eventdata_transactionid
group by A.year, A.month
order by A.year, A.month
 
-- WALLET TOPUP FRAUD CNT & AMT BASED ON TXN MONTH
select A.year, A.month, count(merchant_reference_id) as count, sum(amt) as amount from 
    (SELECT merchant_reference_id, amount/100 as amt, year, month
    FROM wallet.transaction_master
    WHERE year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND category = 'TOPUP' AND txn_type = 'CREDIT'
    AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
    GROUP BY merchant_reference_id, amount/100, year, month)A
INNER JOIN
    (select eventdata_transactionid, year, month
    from foxtrot_stream.cerebro_default 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND (eventdata_fraudtxn = 1 or eventdata_action = 'FRAUD')
    and eventtype = 'MARK_TRANSACTION'
    GROUP BY eventdata_transactionid, year, month)C
on A.merchant_reference_id = C.eventdata_transactionid
group by A.year, A.month
order by A.year, A.month
 
-- EGV PURCHASE FRAUD CNT & AMT BASED ON REPORTED MONTH
select C.year, C.month, count(eventdata_transactionid) as count, sum(amt) as amount from 
    (select merchant_transaction_id, card_number, original_balance/100 AS amt, year, month
    from egv.gift_cards 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND program_id = 'PHONEPEGC' AND tenant_id LIKE 'PHONEPE%' AND merchant_id = 'PHONEPEGC')A
INNER JOIN
    (select transaction_id, global_payment_id, amount
    from payment.transactions 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    and state='COMPLETED' and error_code='SUCCESS' and backend_error_code='SUCCESS'
    AND flow IN ('CONSUMER_TO_MERCHANT_V2', 'CONSUMER_TO_MERCHANT'))B
on A.merchant_transaction_id = B.global_payment_id
INNER JOIN
    (select eventdata_transactionid, year, month
    from foxtrot_stream.cerebro_default 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND (eventdata_fraudtxn = 1 or eventdata_action = 'FRAUD')
    and eventtype = 'MARK_TRANSACTION'
    GROUP BY eventdata_transactionid, year, month)C
on B.transaction_id = C.eventdata_transactionid
group by C.year, C.month
order by C.year, C.month
 
-- WALLET TOPUP FRAUD CNT & AMT BASED ON REPORTED MONTH
select C.year, C.month, count(merchant_reference_id) as count, sum(amt) as amount from 
    (SELECT merchant_reference_id, amount/100 as amt
    FROM wallet.transaction_master
    WHERE year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND category = 'TOPUP' AND txn_type = 'CREDIT'
    AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
    GROUP BY merchant_reference_id, amount/100)A
INNER JOIN
    (select eventdata_transactionid, year, month
    from foxtrot_stream.cerebro_default 
    where year = 2024 AND month = prevMonthNo
    AND (eventdata_fraudtxn = 1 or eventdata_action = 'FRAUD')
    and eventtype = 'MARK_TRANSACTION'
    GROUP BY eventdata_transactionid, year, month)C
on A.merchant_reference_id = C.eventdata_transactionid
group by C.year, C.month
order by C.year, C.month
 
---------------------------------------------
-- EGV REDEMPTION BASE ON TXN MONTH
select year, month, COUNT(DISTINCT substr(merchant_transaction_id, 0, instr(merchant_transaction_id,':')-1)) as transaction_id , sum(amount/100) as amt
from egv.gift_card_histories
where operation like 'REDEEM'
and year = 2024 AND month = prevMonthNo
GROUP BY year, month
 
-- WALLET REDEMPTION BASED ON TXN MONTH
SELECT year, month, COUNT(txn_id), SUM(amount/100) as amt
FROM wallet.transaction_master
WHERE year = 2024 AND month = prevMonthNo
AND category = 'ORDER' AND txn_type = 'DEBIT'
AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
GROUP BY year, month
 
-- EGV REDEEM FRAUD BASED ON TXN MONTH
SELECT B.year, B.month, COUNT(B.transaction_id), SUM(B.amt) as fraud_amt FROM
    (select eventdata_transactionid, year, month
    from foxtrot_stream.cerebro_default 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND (eventdata_fraudtxn = 1 or eventdata_action = 'FRAUD')
    and eventtype = 'MARK_TRANSACTION'
    GROUP BY eventdata_transactionid, year, month)A
INNER JOIN
    (select substr(merchant_transaction_id, 0, instr(merchant_transaction_id,':')-1) as transaction_id , sum(amount/100) as amt, year, month
    from egv.gift_card_histories
    where operation like 'REDEEM'
    and year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    GROUP BY substr(merchant_transaction_id, 0, instr(merchant_transaction_id,':')-1), year, month)B
ON A.eventdata_transactionid = B.transaction_id
GROUP BY B.year, B.month
 
-- WALLET REDEEM FRAUD BASED ON TXN MONTH
SELECT B.year, B.month, COUNT(B.txn_id), SUM(B.amt) as fraud_amt FROM
    (select eventdata_transactionid, year, month
    from foxtrot_stream.cerebro_default 
    where year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND (eventdata_fraudtxn = 1 or eventdata_action = 'FRAUD')
    and eventtype = 'MARK_TRANSACTION'
    GROUP BY eventdata_transactionid, year, month)A
INNER JOIN
    (SELECT txn_id, (amount/100) as amt, year, month
    FROM wallet.transaction_master
    WHERE year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND category = 'ORDER' AND txn_type = 'DEBIT'
    AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS')B
ON A.eventdata_transactionid = B.txn_id
GROUP BY B.year, B.month
 
-- EGV REDEEM FRAUD BASED ON REPORTED MONTH
SELECT A.year, A.month, COUNT(B.transaction_id), SUM(B.amt) as fraud_amt FROM
    (select eventdata_transactionid, year, month
    from foxtrot_stream.cerebro_default 
    where year = 2024 AND month = prevMonthNo 
    AND (eventdata_fraudtxn = 1 or eventdata_action = 'FRAUD')
    and eventtype = 'MARK_TRANSACTION'
    GROUP BY eventdata_transactionid, year, month)A
INNER JOIN
    (select substr(merchant_transaction_id, 0, instr(merchant_transaction_id,':')-1) as transaction_id , sum(amount/100) as amt, year, month
    from egv.gift_card_histories
    where operation like 'REDEEM'
    and year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo 
    GROUP BY substr(merchant_transaction_id, 0, instr(merchant_transaction_id,':')-1), year, month)B
ON A.eventdata_transactionid = B.transaction_id
GROUP BY A.year, A.month
 
-- WALLET REDEEM FRAUD BASED ON REPORTED MONTH
SELECT A.year, A.month, COUNT(B.txn_id), SUM(B.amt) as fraud_amt FROM
    (select eventdata_transactionid, year, month
    from foxtrot_stream.cerebro_default 
    where year = 2024 AND month = prevMonthNo
    AND (eventdata_fraudtxn = 1 or eventdata_action = 'FRAUD')
    and eventtype = 'MARK_TRANSACTION'
    GROUP BY eventdata_transactionid, year, month)A
INNER JOIN
    (SELECT txn_id, (amount/100) as amt, year, month
    FROM wallet.transaction_master
    WHERE year = 2024 AND month BETWEEN (prevMonthNo - 3) AND prevMonthNo
    AND category = 'ORDER' AND txn_type = 'DEBIT'
    AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS')B
ON A.eventdata_transactionid = B.txn_id
GROUP BY A.year, A.month