EGV REPORT NEW

-- 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 IN (2022, 2023) -- AND month BETWEEN (9-3) AND 9
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 IN (2022, 2023) -- AND month BETWEEN 3 AND 9
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 IN (2022, 2023) -- AND month BETWEEN (9-3) AND 9
    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 IN (2022, 2023) -- AND month BETWEEN (9-3) AND 9
    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 IN (2022, 2023) -- AND month = 9
    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 IN (2022, 2023) -- AND month BETWEEN 3 AND 9
    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 IN (2022, 2023) -- AND month BETWEEN 6 AND 9
    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 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 IN (2022, 2023) -- AND month BETWEEN (9-3) AND 9
    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 IN (2022, 2023) -- AND month BETWEEN (9-3) AND 9
    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 IN (2022, 2023) -- AND month = 9
    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 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 IN (2022, 2023) -- AND month BETWEEN 3 AND 9
    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 IN (2022, 2023) -- AND month BETWEEN 6 AND 9
    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 REDEEM FRAUD BASED ON TXN 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 IN (2022, 2023) -- AND month BETWEEN 6 AND 9
    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) as amt, year, month
    from egv.gift_card_histories
    where operation like 'REDEEM'
    and year IN (2022, 2023)
    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

-- EGV REDEEM FRAUD BASED ON REPORTED 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 IN (2022, 2023) -- AND month BETWEEN 6 AND 9
    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) as amt, year, month
    from egv.gift_card_histories
    where operation like 'REDEEM'
    and year IN (2022, 2023)
    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 IN (2022, 2023) -- AND month BETWEEN 6 AND 9
    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 IN (2022, 2023)
    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

-- 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 IN (2022, 2023) -- AND month BETWEEN 6 AND 9
    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 IN (2022, 2023)
    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