banksha weekly
Mon Feb 06 2023 11:02:46 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive) set tez.queue.name=default; set hive.execution.engine=tez; select DISTINCT DATE_SUB('2023-01-20',7) starting_date , DATE_SUB('2023-01-20',1) ending_date , active_days , 'weekly' as date_range , X.identifier identifier , 'Banksha_greater_5L' red_flag , X.value value , X.comment as comment , (case when e.identifier is null AND ee.identifier is null then 'New' else 'Repeat' end) as new_or_repeat , 'AML' AS `group` , 'FRA' AS `type` , 'Alerts' AS type_fra , 'User' AS issue_type , 'UPI' AS sub_issue_type , '2023-01-20' AS run_date from (select A.receiverbankaccountsha AS identifier ,count(DISTINCT B.updated_date) AS active_days ,sum(B.amt) AS value ,COUNT(DISTINCT B.receiveruser) AS comment from (select c.receiverbankaccountsha, count(distinct c.receiveruser) cnt --, SUM(amt) as totAmtReceived from (select receiverbankaccountsha, receiveruser, sum(totaltransactionamount) as amt from fraud.transaction_details_v3 where updated_date IN (date_sub ('2023-01-20', 7), date_sub ('2023-01-20', 1)) and receivertype = 'INTERNAL_USER' and errorcode = 'SUCCESS' AND receiverbankaccountsha <> '' group by receiverbankaccountsha, receiveruser having amt > 100000)c group by c.receiverbankaccountsha having cnt > 1 -- AND totAmtReceived > 500000 )A INNER JOIN (select receiverbankaccountsha, receiveruser, transaction_id, totaltransactionamount AS amt, updated_date from fraud.transaction_details_v3 where updated_date IN (date_sub ('2023-01-20', 7), date_sub ('2023-01-20', 1)) and receivertype = 'INTERNAL_USER' and errorcode = 'SUCCESS' AND receiverbankaccountsha <> '')B on A.receiverbankaccountsha = B.receiverbankaccountsha group by A.receiverbankaccountsha ORDER BY comment DESC, value DESC LIMIT 10)X left join (select identifier from fraud.aml_freshdesk_month) e on X.identifier = e.identifier left join (select identifier from fraud.aml_freshdesk) ee on X.identifier = ee.identifier ORDER BY comment DESC, value DESC LIMIT 5
Comments