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