RFI 15

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

-- rule 15
SELECT A.receiveruser
-- , A.active_days_previous
, A.amount_total_previous
, A.txn_cnt_previous
, A.sender_vpa_cnt_previous
, B.active_days_current
, B.amount_total_current
, B.txn_cnt_current
, B.sender_vpa_cnt_current
, (A.amount_total_previous-B.amount_total_current)/B.amount_total_current as percent_dip_in_amount
FROM
    (SELECT receiveruser
    , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02', 33+30) AND DATE_SUB('2023-05-02', 33) THEN updated_date ELSE NULL END) as active_days_previous
    , sum(CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02', 33+30) AND DATE_SUB('2023-05-02', 33) THEN totaltransactionamount ELSE 0 END) as amount_total_previous
    , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02', 33+30) AND DATE_SUB('2023-05-02', 33) THEN transaction_id ELSE NULL END) as txn_cnt_previous
    , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02', 33+30) AND DATE_SUB('2023-05-02', 33) THEN senderuserid ELSE NULL END) as sender_vpa_cnt_previous
    , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02', 32) AND DATE_SUB('2023-05-02', 2) THEN updated_date ELSE NULL END) as active_days_current
    , sum(CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02', 32) AND DATE_SUB('2023-05-02', 2) THEN totaltransactionamount ELSE 0 END) as amount_total_current
    , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02', 32) AND DATE_SUB('2023-05-02', 2) THEN transaction_id ELSE NULL END) as txn_cnt_current
    , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02', 32) AND DATE_SUB('2023-05-02', 2) THEN senderuserid ELSE NULL END) as sender_vpa_cnt_current
    from fraud.transaction_details_v3
    where updated_date between '2023-03-01' and '2023-03-31' 
    and receivertype = 'MERCHANT' and (receiversubtype In ('P2P_MERCHANT','P2M_LIMITED') or origination_mode in ('B2B_PG'))
    and backend_errorcode = 'SUCCESS' and mcc not in ('5944', '5541')
    group by receiveruser
    Having txn_cnt_previous > 100
    AND  ((A.sender_vpa_cnt_previous - B.sender_vpa_cnt_current) > 500))A
INNER JOIN
    (select merchant_id, count(distinct store_id) as store_cnt
    from fraud.offline_merchant_profile_v2
    group by merchant_id
    having store_cnt <= 2)C
ON B.receiveruser = C.merchant_id
where
and (A.amount_total_previous-B.amount_total_current) > 2000000
and ((A.amount_total_previous-B.amount_total_current)/B.amount_total_current) > 0.8