%jdbc(hive)
SET tez.am.resource.memory.mb = 51200;
SET tez.queue.name = fra_analytics;
SET hive.execution.engine = tez;

SELECT A.receiveruser, repeat_amt, repeat_count, total_amount, total_count, rnk,
    ((repeat_amt * repeat_count) / total_amount) as amount_perc,
    repeat_count / total_count as count_perc
FROM
    (SELECT receiveruser,repeat_amt,repeat_count, rnk
    FROM 
(
    SELECT receiveruser, totaltransactionamount as repeat_amt, COUNT(DISTINCT transaction_id) as repeat_count,
    ROW_NUMBER() OVER (PARTITION BY receiveruser,totaltransactionamount ORDER BY COUNT(DISTINCT transaction_id) DESC)  rnk

    FROM fraud.transaction_details_v3
        WHERE updated_date BETWEEN '2023-04-01' AND '2023-04-30'
            AND pay_transaction_status = 'COMPLETED'
            AND sendertype = 'INTERNAL_USER'
            AND workflowtype IN ('CONSUMER_TO_MERCHANT', 'CONSUMER_TO_MERCHANT_V2')
            AND (receiversubtype IN ('P2P_MERCHANT', 'P2M_LIMITED') OR origination_mode = 'B2B_PG')
    GROUP BY totaltransactionamount, receiveruser   
    )X
    where rnk < 3) A
LEFT JOIN
    (SELECT receiveruser, SUM(totaltransactionamount) as total_amount, COUNT(DISTINCT transaction_id) as total_count
    FROM fraud.transaction_details_v3
    WHERE updated_date BETWEEN '2023-04-01' AND '2023-04-30'
        AND pay_transaction_status = 'COMPLETED'
        AND sendertype = 'INTERNAL_USER'
        AND workflowtype IN ('CONSUMER_TO_MERCHANT', 'CONSUMER_TO_MERCHANT_V2')
        AND (receiversubtype IN ('P2P_MERCHANT', 'P2M_LIMITED') OR origination_mode = 'B2B_PG')
    GROUP BY receiveruser
    having COUNT(DISTINCT transaction_id)>100) B
ON A.receiveruser = B.receiveruser
--WHERE ((repeat_amt * repeat_count) / total_amount) > 0.3
--    AND repeat_count / total_count > 0.3
ORDER BY count_perc DESC, amount_perc DESC
limit 1000;