%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