high_spend_on_cc_monthly TO MONTHLY

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