master table for DISTINCTS

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

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) sender: ACTIVE DAYS -- 5 mins

-- SELECT senderuserid, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND sendertype = 'INTERNAL_USER' AND upi_flag = TRUE 
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY senderuserid, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) sender: MX CNT -- 2 MINS
-- SELECT senderuserid, receiveruser, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND sendertype = 'INTERNAL_USER' AND upi_flag = TRUE AND receivertype = 'MERCHANT'
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY senderuserid, receiveruser, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) sender: Number of receiver Vpas (Consumers) -- 10 mins
-- SELECT senderuserid, reciver_vpa, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND sendertype = 'INTERNAL_USER' AND upi_flag = TRUE
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY senderuserid, reciver_vpa, updated_date

-- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Locations of user as sender -- 1.5 mins
SELECT senderuserid, sender_city, updated_date
FROM fraud.transaction_details_v3
WHERE updated_date = '2023-07-09'
AND sendertype = 'INTERNAL_USER' AND upi_flag = TRUE 
AND sender_city IS NOT NULL AND sender_city <> ''
AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
GROUP BY senderuserid, sender_city, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Number of bank accounts used to send money from -- 5 mins
-- SELECT senderuserid, senderbankaccountsha, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND sendertype = 'INTERNAL_USER' AND upi_flag = TRUE AND senderbankaccountsha IS NOT NULL
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY senderuserid, senderbankaccountsha, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) receiver pov ACTIVE DAYS -- 15 mins
-- SELECT receiveruser, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND receivertype = 'INTERNAL_USER' AND upi_flag = TRUE 
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY receiveruser, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Number of sender vpas -- 1.5 mins
-- SELECT receiveruser, sender_vpa, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND receivertype = 'INTERNAL_USER' AND upi_flag = TRUE
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY receiveruser, sender_vpa, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Locations of senders sending to receiver -- 3 mins
-- SELECT receiveruser, sender_city, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND receivertype = 'INTERNAL_USER' AND upi_flag = TRUE AND sender_city IS NOT NULL AND sender_city <> ''
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY receiveruser, sender_city, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Number of bank account used to receive money --1.5 mins
-- SELECT receiveruser, receiverbankaccountsha, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND receivertype = 'INTERNAL_USER' AND upi_flag = TRUE AND receiverbankaccountsha IS NOT NULL AND receiverbankaccountsha <> ''
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY receiveruser, receiverbankaccountsha, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Number of cards used to transact
-- SELECT senderuserid, masked_card_number, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND sendertype = 'INTERNAL_USER' AND card_flag = TRUE AND masked_card_number IS NOT NULL
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY senderuserid, masked_card_number, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Number of merchants paid towards -- 3 mins
-- SELECT senderuserid, receiveruser, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND sendertype = 'INTERNAL_USER' AND card_flag = TRUE AND masked_card_number IS NOT NULL AND receivertype = 'MERCHANT'
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY senderuserid, receiveruser, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Number of sub account provider IDs received payment in Card -- 1.5 min
-- SELECT senderuserid, accountingproviderid, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND sendertype = 'INTERNAL_USER' AND card_flag = TRUE AND masked_card_number IS NOT NULL AND receivertype = 'MERCHANT' 
-- AND accountingproviderid IS NOT NULL AND accountingproviderid <> ''
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY senderuserid, accountingproviderid, updated_date

-- -- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Total number of instruments used to topup wallet -- 2 min
-- SELECT senderuserid, coalesce(senderbankaccountsha,masked_card_number) as accountsha_or_card, updated_date
-- FROM fraud.transaction_details_v3
-- WHERE updated_date = '2023-07-09'
-- AND sendertype = 'INTERNAL_USER' AND receiveruser = 'PHONEPEWALLETTOPUP' 
-- AND (senderbankaccountsha IS NOT NULL or masked_card_number IS NOT NULL)
-- AND (upi_flag = TRUE OR card_flag = TRUE)
-- AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
-- GROUP BY senderuserid, senderbankaccountsha, masked_card_number, updated_date
-- HAVING coalesce(senderbankaccountsha,masked_card_number) IS NOT NULL AND coalesce(senderbankaccountsha,masked_card_number) <> ''

-- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) wallet:Number of merchants redeemed at --2 min
SELECT senderuserid, receiveruser, updated_date
FROM fraud.transaction_details_v3
WHERE updated_date = '2023-07-09'
    AND sendertype = 'INTERNAL_USER' AND wallet_flag = TRUE AND receivertype = 'MERCHANT'
    AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
GROUP BY senderuserid, receiveruser, updated_date

-- MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) Number of Active days (Wallet redemption) -- 1 min
SELECT senderuserid, updated_date
FROM fraud.transaction_details_v3
WHERE updated_date = '2023-07-09'
AND sendertype = 'INTERNAL_USER' AND wallet_flag = TRUE 
AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
GROUP BY senderuserid, updated_date

--MASTER TABLE DAILY INSERT FOR COUNT(DISTINCT) MX CNT EGV --2 mins
SELECT senderuserid, receiveruser, updated_date
FROM fraud.transaction_details_v3
WHERE updated_date = '2023-07-09'
AND sendertype = 'INTERNAL_USER' 
AND upi_flag = false
AND card_flag = false
AND wallet_flag = false
AND freecharge_flag = false
AND jio_flag = false
AND airtel_flag = false
AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
GROUP BY senderuserid, receiveruser, updated_date