avg_vpa_amt : no duplicates

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

select distinct DATE_SUB('2022-11-11',7) starting_date, DATE_SUB('2022-11-11',1) ending_date
,a.active_days active_days
,'weekly' date_range
,a.identifier identifier
,'avg_vpa_amt' red_flag
,a.amt/a.vpa value
,a.vpa comment
,case when d.identifier is null then 'New' else 'Repeat' end as new_or_repeat
,`group`
,`type`
,type_fra
,issue_type
,sub_issue_type
,'2022-11-11' as run_date from
    (select receiveruser identifier ,count(transaction_id) txn ,sum(totaltransactionamount) amt ,count(distinct sender_vpa) vpa
    ,count(distinct updated_date) active_days
    ,'AML' `group`
    ,'FRA' `type`
    ,'Alerts' type_fra
    ,'User' issue_type
    ,'UPI' sub_issue_type
    from fraud.transaction_details_v3
    where updated_date BETWEEN date_sub('2022-11-11',7) AND date_sub('2022-11-11',1)
    and errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED'
    and receivertype = 'INTERNAL_USER'
    and receiveruser != ''
    group by receiveruser)a
left join
    (select DISTINCT identifier from fraud.aml_freshdesk WHERE run_date < '2022-11-11') d
on a.identifier = d.identifier
left join
    (select user_ext_id AS identifier, MAX(updated) as BLdate 
    from users.users 
    where blacklisted = 1 and blacklist_reason = 'SAM'
    GROUP BY user_ext_id HAVING BLdate < '2022-11-11')f
On a.identifier = f.identifier
left join 
    (select identifier from fraud.aml_receiverside_temporary UNION select identifier from fraud.aml_receiverside_External_temporary) g 
On d.identifier = g.identifier
where f.identifier is NULL AND g.identifier IS NULL AND a.vpa < 15
ORDER BY value DESC
LIMIT 5