RFI 12
Tue May 30 2023 09:00:00 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%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
Comments