RFI 12

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

-- PPI RFI LIST 1 RULE 12
-- FEB
SELECT A.wallet_id, B.inactive_days
, SUM(A.amt)/2 AS old_monthly_avg_amt
, C.amt
, SUM(A.txn_count)/2 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
, ((C.amt / C.txn_count) - AVG(A.amt / A.txn_count))/AVG(A.amt / A.txn_count) as ATS_increase
FROM
    -- pre dormancy
    (SELECT wallet_id, month as monthNo
    , count(distinct txn_id) as txn_count, SUM(amount) as amt, COUNT(DISTINCT Date(txn_time)) as active_days
    FROM wallet.transaction_master
    WHERE year >= year(DATE_SUB('2023-03-02', 120+2+61))
    AND month >= month(DATE_SUB('2023-03-02', 120+2+61)) AND month <= month(DATE_SUB('2023-03-02', 120+2+1))
    AND Date(txn_time) BETWEEN DATE_SUB('2023-03-02', 120+2+61) AND DATE_SUB('2023-03-02', 120+2+1)
    AND category = 'ORDER' AND txn_type = 'DEBIT' 
    AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
    GROUP BY wallet_id, month)A
INNER JOIN
    (SELECT wallet_id, inactive_days FROM 
        (SELECT wallet_id, txn_time1, txn_time2, inactive_days
        , row_number() OVER (PARTITION BY wallet_id ORDER BY inactive_days DESC, txn_time1) as rn FROM
            (SELECT wallet_id
            , txn_time AS txn_time1
            , (lag(txn_time) over (partition by wallet_id order by txn_time)) as txn_time2 
            , DATEDIFF(txn_time, (lag(txn_time) over (partition by wallet_id order by txn_time))) AS inactive_days FROM
                (
                SELECT DISTINCT wallet_id, Date(txn_time) AS txn_time
                FROM wallet.transaction_master
                WHERE year >= year(DATE_SUB('2023-03-02', 120+2))
                AND (month >= month(DATE_SUB('2023-03-02', 120+2)) OR month <= month(DATE_SUB('2023-03-02', 2)))
                AND Date(txn_time) BETWEEN DATE_SUB('2023-03-02', 120+2) AND DATE_SUB('2023-03-02', 2)
                AND category = 'ORDER' AND txn_type = 'DEBIT' 
                AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS')X)Y
            WHERE txn_time2 IS NOT NULL AND inactive_days 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.wallet_id = B.wallet_id 
INNER JOIN
    -- post dormancy
    (SELECT wallet_id, count(distinct txn_id) as txn_count, SUM(amount) as amt, COUNT(DISTINCT Date(txn_time)) as active_days
    FROM wallet.transaction_master
    WHERE year >= year(DATE_SUB('2023-03-02', 32))
    AND month >= month(DATE_SUB('2023-03-02', 32)) AND month <= month(DATE_SUB('2023-03-02', 2))
    AND Date(txn_time) BETWEEN DATE_SUB('2023-03-02', 32) AND DATE_SUB('2023-03-02', 2)
    AND category = 'ORDER' AND txn_type = 'DEBIT' 
    AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
    GROUP BY wallet_id, month)C
ON A.wallet_id = C.wallet_id 
-- -- WHERE C.amt > 250000
GROUP BY A.wallet_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
LIMIT 100