international remittances WEEKLY TO MONTHLY

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

SELECT DISTINCT DATE_SUB('2023-07-02', 32) AS starting_date
,DATE_SUB('2023-07-02', 2) AS ending_date
,active_days
,'weekly' as date_range
,identifier
,'international_remittances' as red_flag
,int_rem_amt as value
,dist_senders as comment
,new_or_repeat
,'AML' as `group`
,'FRA' as `type`
,'Alerts' as type_fra
,'User' as issue_type
,'UPI' as sub_issue_type
,'2023-07-02' as run_date
FROM
    (SELECT A.identifier
    , A.int_rem_amt
    , A.no_of_int_rem_txns
    , A.tot_incoming_txns
    , A.perc_int_txns
    , A.dist_senders
    , A.active_days
    , case when (ee.identifier is null AND e.identifier IS NULL) then 'New' else 'Repeat' end as new_or_repeat FROM
        (SELECT receiveruser AS identifier
        , SUM(IF(upper(message) = 'RDA CR TO KYC COMPLIANT ACCT ONLY', totaltransactionamount, 0)) as int_rem_amt
        , COUNT(DISTINCT IF(upper(message) = 'RDA CR TO KYC COMPLIANT ACCT ONLY', transaction_id, NULL)) as no_of_int_rem_txns
        , COUNT(DISTINCT transaction_id) as tot_incoming_txns
        , COUNT(IF(upper(message) = 'RDA CR TO KYC COMPLIANT ACCT ONLY', transaction_id, NULL)) / COUNT(DISTINCT transaction_id) as perc_int_txns
        , COUNT (DISTINCT IF(upper(message) = 'RDA CR TO KYC COMPLIANT ACCT ONLY', sendernameonbankaccount, NULL)) as dist_senders
        , COUNT(DISTINCT IF(upper(message) = 'RDA CR TO KYC COMPLIANT ACCT ONLY', updated_date, NULL)) as active_days
        FROM fraud.transaction_details_v3
        WHERE date(updated_date) BETWEEN DATE_SUB('2023-07-02', 32) AND DATE_SUB('2023-07-02', 2)
        AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' 
        AND receivertype = 'INTERNAL_USER' AND receiveruser <> ''
        GROUP BY receiveruser
        HAVING COUNT (CASE WHEN  upper(message) = 'RDA CR TO KYC COMPLIANT ACCT ONLY' THEN transaction_id END)/
            COUNT (DISTINCT transaction_id) > 0.5)A
    left join
        (select DISTINCT identifier from fraud.aml_freshdesk WHERE run_date < DATE_SUB('2023-07-02', 32))e
    On A.identifier = e.identifier
    left join
        (select DISTINCT identifier from fraud.aml_freshdesk_month WHERE run_date < DATE_SUB('2023-07-02', 32)) ee
    on A.identifier = ee.identifier
    left join
        (select user_ext_id AS identifier
        from users.users 
        where blacklisted = 1 and blacklist_reason = 'SAM'
        AND updated < DATE_SUB('2023-07-02', 32))f
    On A.identifier = f.identifier
    -- left join 
    --     (select identifier from fraud.avg_vpa_amt
    --     UNION select identifier from fraud.Merchant_high_nonqr
    --     UNION select identifier from fraud.high_spend_on_cc_weekly
    --     UNION select identifier from fraud.mult_cc_wallet_topup_weekly) g 
    -- On a.identifier = g.identifier
    where A.identifier is NOT NULL AND f.identifier is NULL -- AND g.identifier IS NULL
    AND e.identifier IS NULL AND ee.identifier IS NULL
    ORDER BY dist_senders desc, perc_int_txns desc, int_rem_amt desc
    LIMIT 8)X
ORDER BY comment DESC, value DESC ;