receiver_decimal_values

 %jdbc(hive)
set tez.queue.name=default;
set hive.execution.engine=tez;

-- CONVERT TO WEEKLY
select DISTINCT '2022-10-28' starting_date --DATE_SUB('{{ds}}',34)
,'2022-11-03' ending_date --DATE_SUB('{{ds}}',4)
,rcvr.rcvr_active_days as active_days
,'weekly' as date_range
,b.rcvr_usr as identifier
,'Receiver_dec_val_80p' as red_flag
,rcvr.amt value
,b.dec_per comment
,b.txn as txns
,(case when e.identifier is null then 'New' else 'Repeat' end) as new_or_repeat
,'AML' AS `group`
,'FRA' AS `type`
,'Alerts' AS type_fra
,'User' AS issue_type
,'UPI' AS sub_issue_type
,'2022-11-04' as run_date from
    (select a.receiveruser rcvr_usr
    ,count(distinct case when a.amt not like '%.0%' then a.txn end) dec_txn
    ,count(distinct a.txn) txn
    ,count(distinct case when a.amt not like '%.0%' then a.txn end)/count(distinct a.txn) dec_per
    ,sum(case when a.amt not like '%.0%' then a.amt end) dec_val
    ,sum(a.amt) val
    from
        (select receiveruser
        ,cast(totaltransactionamount as string) amt
        ,transaction_id txn
        from fraud.transaction_details_v3
        where receivertype = 'INTERNAL_USER' AND receiveruser IS NOT NULL
        and Date(updated_date) BETWEEN '2022-10-28' AND '2022-11-03'
        and upi_flag = true
        and sendertype <> 'MERCHANT'
        and errorcode = 'SUCCESS')a
    group by a.receiveruser
    having count(distinct case when a.amt not like '%.0%' then a.txn end)/count(distinct a.txn) > 0.8
    and sum(case when a.amt not like '%.0%' then a.amt end) > 100000 --1000000
    order by val, txn desc
    limit 10)b
INNER JOIN
    (select receiveruser
    ,count(distinct updated_date) rcvr_active_days
    ,count(distinct transaction_id) txn
    ,sum(totaltransactionamount) amt
    ,sum(totaltransactionamount)/count(distinct transaction_id) rcvr_avg_txn_val
    ,count(distinct case when hour(transaction_time) >= 22 or hour(transaction_time) <= 6 then transaction_id end) rcvr_off_hour_txn
    ,count(distinct senderuserid) ct_senders
    from fraud.transaction_details_v3
    where Date(updated_date) BETWEEN '2022-10-28' AND '2022-11-03'
    and upi_flag = true
    and errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED'
    group by receiveruser 
    HAVING count(distinct transaction_id) > 1)rcvr
on b.rcvr_usr = rcvr.receiveruser
left join 
    (select identifier from fraud.aml_freshdesk_month UNION select identifier from fraud.aml_freshdesk) e
on b.rcvr_usr = 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 b.rcvr_usr = f.identifier
where b.rcvr_usr is NOT NULL AND f.identifier is NULL
order by rcvr.amt desc limit 5