%jdbc(hive) set tez.queue.name=fra_analytics; set hive.execution.engine=tez; select DISTINCT DATE_SUB('2023-07-02',32) starting_date ,DATE_SUB('2023-07-02',2) ending_date ,a.active_days active_days ,'monthly' date_range ,a.identifier identifier ,'high_spend_on_cc_monthly' red_flag ,a.value value ,a.comment comment ,case when (e.identifier is null AND ee.identifier IS NULL) then 'New' else 'Repeat' end as new_or_repeat ,'AML' `group` ,'FRA' `type` ,'Alerts' type_fra ,'User' issue_type ,'Cards' sub_issue_type ,'2023-07-02' as run_date from (select senderuserid identifier ,count(distinct updated_date) active_days ,count(distinct transaction_id) comment ,sum(totaltransactionamount) value from fraud.transaction_details_v3 where Date(updated_date) BETWEEN DATE_SUB('2023-07-02',32) AND DATE_SUB('2023-07-02',2) and card_flag = TRUE and errorcode = 'SUCCESS' and sendertype = 'INTERNAL_USER' and sendercard_type = 'CREDIT_CARD' and transfermode = 'PEER_TO_MERCHANT' AND receiversubtype = 'ONLINE_AGGREGATOR' group by senderuserid order by value desc limit 10)a LEFT JOIN (select identifier from fraud.aml_freshdesk_month)e on a.identifier = e.identifier LEFT JOIN (select identifier from fraud.aml_freshdesk)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 < '2023-07-21')f On a.identifier = f.identifier -- left join -- (select identifier from fraud.avg_vpa_amt -- UNION SELECT identifier FROM fraud.Merchant_high_nonqr) g -- On a.identifier = g.identifier where a.identifier IS NOT NULL AND f.identifier IS NULL --AND g.identifier IS NULL order by a.value desc limit 8 ;
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter