Mona's topup rule: TOPUP & redemption TXNS
Thu Jan 12 2023 05:47:45 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive) set tez.queue.name=default; set hive.execution.engine=tez; -- WALLET TOPUP TXNS SELECT DISTINCT B.current_device_fingerprint, A.senderuserid, B.wallet_id, A.transaction_id, A.amt, A.updated_date , SUM(A.amt) OVER (PARTITION BY B.current_device_fingerprint) AS totAmt FROM (SELECT senderuserid, transaction_id, totaltransactionamount as amt, updated_date FROM fraud.transaction_details_v3 where updated_date BETWEEN '2022-11-30' AND '2022-12-30' and sendertype = 'INTERNAL_USER' AND workflowtype = 'CONSUMER_TO_MERCHANT' and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' and receiveruser in ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP'))A INNER JOIN (SELECT user_id, current_device_fingerprint, wallet_id FROM edw_shared.user_dimension WHERE wallet_id IS NOT NULL AND current_device_fingerprint IN ('JbOUrGZFKY9pvL2gnU9IvfogNehIFtcosN5VeObSE', 'tOLu1FbOqzRjPFK5HqJdcieOOl1PbBOhaAEahOF2w0'))B ON A.senderuserid = B.user_id INNER JOIN (SELECT DISTINCT senderuserid FROM fraud.transaction_details_v3 WHERE updated_date BETWEEN '2022-11-30' AND '2022-12-30' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND wallet_flag = true AND receiversubtype IN ('P2M_LIMITED', 'ONLINE_AGGREGATOR', 'ONLINE_MERCHANT') and receiveruser NOT IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP'))C ON A.senderuserid = C.senderuserid ; -- redemption to merchant SELECT B.current_device_fingerprint, senderuserid, wallet_id, receiveruser, receiversubtype, amt, SUM(amt) OVER (PARTITION BY B.current_device_fingerprint) AS tot, amt / SUM(amt) OVER (PARTITION BY B.current_device_fingerprint) as PercRedeemed FROM (SELECT senderuserid, receiveruser, totaltransactionamount as amt, receiversubtype, transaction_id FROM fraud.transaction_details_v3 WHERE updated_date BETWEEN '2022-11-30' AND '2022-12-30' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND wallet_flag = true AND receiversubtype IN ('P2M_LIMITED', 'ONLINE_AGGREGATOR', 'ONLINE_MERCHANT'))A INNER JOIN (SELECT DISTINCT user_id, current_device_fingerprint, wallet_id FROM edw_shared.user_dimension WHERE wallet_id IS NOT NULL AND current_device_fingerprint IN ('JbOUrGZFKY9pvL2gnU9IvfogNehIFtcosN5VeObSE', 'tOLu1FbOqzRjPFK5HqJdcieOOl1PbBOhaAEahOF2w0'))B ON A.senderuserid = B.user_id ;
Comments