SELECT *
from egv.transaction_data 
where day(updated_at) between '14' AND '27' and year = 2022 and month = 10
and operation like 'REDEEM%' and user_id IN ('U1903082105095713436208')

SELECT * 
from egv.gift_cards
where user_id IN ('U1903082105095713436208') OR purchasing_user_id in ('U1903082105095713436208')
and day(updated_at) between '14' AND '27' and year = 2022 and month = 1


SELECT user_id , sum(execution_amount) as total_amount_reedemed
from egv.transaction_data 
where day(updated_at) between '14' AND '27' and year = 2022 and month = 10
and operation like 'REDEEM%' 
group by user_id 
order by total_amount_reedemed desc 
limit 5 


set tez.queue.name=fra_analytics;
set hive.execution.engine=tez;


DROP TABLE if exists fraud.EGV_high_spent;
 
create table fraud.EGV_high_spent
STORED AS ORC
TBLPROPERTIES ("orc.compress" = "ZLIB")
as
SELECT distinct DATE_SUB('{{next_ds}}',7) starting_date 
,DATE_SUB('{{next_ds}}',1) ending_date
,active_days 
,'weekly' as date_range
,identifier
,'EGV_high_spent' red_flag 
,value
,'INR' comment
-- ,(case when d.identifier is null then 'New' else 'Repeat' end) as new_or_repeat
,'New' as new_or_repeat 
,'AML' as `group`
,'FRA' as `type`
,'Alerts' as type_fra
,'User' as issue_type
,'UPI' as sub_issue_type
,'{{next_ds}}' as run_date
from
    (select a.user_id as identifier
    ,a.active_days active_days
    ,a.value 
    from
        (SELECT  DATE_SUB('{{next_ds}}',7) starting_date ,
        DATE_SUB('{{next_ds}}',1) ending_date,
        user_id ,
        count(distinct date(updated_at)) as active_days,
        sum(execution_amount) as value
        from egv.transaction_data 
        where date(updated_at) between DATE_SUB('{{next_ds}}',7) AND DATE_SUB('{{next_ds}}',1) 
        and operation like 'REDEEM%' 
        group by user_id)a
     LEFT JOIN
        (select max(updated) tm, user_ext_id
        from users.users where blacklisted = 1
        and blacklist_reason = 'SAM'
        group by  user_ext_id)b 
    on a.user_id = b.user_ext_id
    left join 
        (select identifier from fraud.aml_freshdesk)d
    on a.user_id = d.identifier
    where b.user_ext_id IS NULL 
    AND d.identifier is null 
    order by value desc limit 3)c