Internation remittances

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

SELECT DISTINCT DATE_SUB('2023-02-03', 7) AS starting_date
,DATE_SUB('2023-02-03', 1) 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-02-03' as run_date
FROM
(SELECT A.*, 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-01-03', 7) AND DATE_SUB('2023-01-03', 1)
        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-01-03', 7))e
On A.identifier = e.identifier
left join
    (select identifier from fraud.aml_freshdesk_month WHERE run_date < DATE_SUB('2023-01-03', 7)) 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-01-03', 7))f
On A.identifier = f.identifier
where A.identifier is NOT NULL AND f.identifier is NULL 
ORDER BY dist_senders desc, perc_int_txns desc, int_rem_amt desc
LIMIT 3)X
ORDER BY comment DESC, value DESC