-- 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
