RFI 7

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

-- RFI LIST 1 RULE 7
-- FEB
SELECT A.receiveruser as merchant_id, A.submerchant_id, B.inactive_days
, AVG(A.amt) AS old_monthly_avg_amt
, C.amt
, AVG(A.txn_count) AS old_monthly_avg_txn_cnt
, C.txn_count
, AVG(A.amt / A.txn_count) as old_avg_ATS
, (C.amt / C.txn_count) as newATS
, AVG(A.txn_count/ A.active_days) as old_avg_txn_per_day
, (C.txn_count/ C.active_days) as new_avg_txn_per_day
, AVG(A.active_days) AS old_avg_active_days
, C.active_days as new_active_days
, AVG(A.sender_cnt) as old_avg_sender_cnt
, C.sender_cnt as new_sender_cnt
, ((C.amt / C.txn_count) - AVG(A.amt / A.txn_count))/AVG(A.amt / A.txn_count) as ATS_increase
FROM
    -- pre dormancy
    (SELECT receiveruser, submerchant_id, month(updated_date) as monthNo
    , count(distinct transaction_id) as txn_count, SUM(totaltransactionamount) as amt, COUNT(DISTINCT updated_date) as active_days, COUNT(DISTINCT senderuserid) AS sender_cnt
    FROM fraud.transaction_details_v3
    WHERE updated_date BETWEEN DATE_SUB('2023-03-02', 120+2+61) AND DATE_SUB('2023-03-02', 120+2+1)
    AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
    AND receivertype = 'MERCHANT'
    GROUP BY receiveruser, submerchant_id, month(updated_date))A
INNER JOIN
    (SELECT merchant_id, submerchant_id, inactive_days FROM 
        (SELECT merchant_id, submerchant_id, txn_time1, txn_time2, inactive_days
        , row_number() OVER (PARTITION BY merchant_id, submerchant_id ORDER BY inactive_days DESC, txn_time1) as rn FROM
            (SELECT merchant_id, submerchant_id 
            , txn_time AS txn_time1, (lag(txn_time) over (partition by merchant_id, submerchant_id  order by txn_time)) as txn_time2 
            , DATEDIFF(txn_time, (lag(txn_time) over (partition by merchant_id, submerchant_id order by txn_time))) AS inactive_days FROM
                (Select DISTINCT receiveruser as merchant_id, submerchant_id, updated_date as txn_time
                FROM fraud.transaction_details_v3
                WHERE updated_date BETWEEN DATE_SUB('2023-03-02', 120+2) AND DATE_SUB('2023-03-02', 2)
                AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
                AND receivertype = 'MERCHANT')X)Y
            WHERE txn_time2 IS NOT NULL)Z
    WHERE rn = 1 AND month(txn_time1) = MONTH(DATE_SUB('2023-03-02', 2)) AND inactive_days >= 90)B
ON A.receiveruser = B.merchant_id AND A.submerchant_id = B.submerchant_id
INNER JOIN
    -- post dormancy
    (SELECT receiveruser, submerchant_id, count(distinct transaction_id) as txn_count, SUM(totaltransactionamount) as amt, COUNT(DISTINCT updated_date) as active_days
    , COUNT(DISTINCT senderuserid) AS sender_cnt
    FROM fraud.transaction_details_v3
    WHERE updated_date BETWEEN DATE_SUB('2023-03-02', 32) AND DATE_SUB('2023-03-02', 2)
    AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
    AND receivertype = 'MERCHANT'
    GROUP BY receiveruser, submerchant_id)C
ON A.receiveruser = C.receiveruser AND A.submerchant_id = C.submerchant_id
-- WHERE C.amt > 250000
GROUP BY A.receiveruser, A.submerchant_id, B.inactive_days, C.amt, C.txn_count, C.active_days, C.sender_cnt 
HAVING AVG(A.txn_count) > 100 -- Old avg amt > 10k
AND AVG(A.txn_count) < C.txn_count --  Old avg txn cnt less than new txn cnt
AND AVG(A.amt / A.txn_count) < (C.amt / C.txn_count) -- old ats less than new ats
AND (((C.amt / C.txn_count) - AVG(A.amt / A.txn_count)) / AVG(A.amt / A.txn_count) * 100) > 50 --Average txn amount deviation > 50%
ORDER BY C.txn_count DESC, ATS_increase DESC