high_spend_on_cc

-- 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