-- HIGH_SPEND_ON_CC WEEKLY select DISTINCT '2022-10-28' AS starting_date -- DATE_SUB('{{ds}}',34) ,'2022-11-03' AS ending_date --DATE_SUB('{{ds}}',4) ,a.identifier identifier ,a.active_days active_days ,a.comment comment ,a.value value ,'high_spend_on_cc' red_flag ,'monthly' date_range ,'AML' `group` ,'FRA' `type` ,'Alerts' type_fra ,'User' issue_type ,'UPI' sub_issue_type ,(case when e.identifier is null then 'New' else 'Repeat' end) as new_or_repeat ,'2022-11-04' run_date from (select eventdata_senderuser identifier ,count(distinct day) active_days ,count(distinct eventdata_transactionid) comment ,sum(eventdata_totaltransactionamount)/100 value from foxtrot_stream.payment_backend_transact where year IN (year('2022-10-28'), year('2022-11-03')) and month IN (month('2022-10-28'), month('2022-11-03')) AND Date(`time`) BETWEEN '2022-10-28' AND '2022-11-03' and eventdata_senderpginvolved = true and eventdata_errorcode = 'SUCCESS' and eventdata_sendertype = 'INTERNAL_USER' and eventdata_senderpginstrument = 'CREDIT_CARD' and eventdata_transfermode = 'PEER_TO_MERCHANT' AND eventdata_receiversubtype = 'ONLINE_AGGREGATOR' group by eventdata_senderuser -- having sum(eventdata_totaltransactionamount)/100 >= 2000000 order by value desc limit 10)a LEFT JOIN (select identifier from fraud.aml_freshdesk_month UNION select identifier from fraud.aml_freshdesk)e on a.identifier = e.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-04')f On a.identifier = f.identifier where a.identifier is NOT NULL AND f.identifier is NULL order by a.value desc limit 5
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