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

select DISTINCT DATE_SUB('2023-01-20',7) starting_date
, DATE_SUB('2023-01-20',1) ending_date
, active_days
, 'weekly' as date_range
, X.identifier identifier
, 'Banksha_greater_5L' red_flag
, X.value value
, X.comment as comment
, (case when e.identifier is null AND ee.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
, '2023-01-20' AS run_date
from
    (select A.receiverbankaccountsha AS identifier
    ,count(DISTINCT B.updated_date) AS active_days
    ,sum(B.amt) AS value
    ,COUNT(DISTINCT B.receiveruser) AS comment from
        (select c.receiverbankaccountsha, count(distinct c.receiveruser) cnt --, SUM(amt) as totAmtReceived
        from
            (select receiverbankaccountsha, receiveruser, sum(totaltransactionamount) as amt
            from fraud.transaction_details_v3
            where updated_date IN (date_sub ('2023-01-20', 7), date_sub ('2023-01-20', 1))
            and receivertype = 'INTERNAL_USER'
            and errorcode = 'SUCCESS'
            AND receiverbankaccountsha <> ''
            group by receiverbankaccountsha, receiveruser
            having amt > 100000)c
        group by c.receiverbankaccountsha having cnt > 1 -- AND totAmtReceived > 500000
        )A
    INNER JOIN
        (select receiverbankaccountsha, receiveruser, transaction_id, totaltransactionamount AS amt, updated_date
        from fraud.transaction_details_v3
        where updated_date IN (date_sub ('2023-01-20', 7), date_sub ('2023-01-20', 1))
        and receivertype = 'INTERNAL_USER'
        and errorcode = 'SUCCESS'
        AND receiverbankaccountsha <> '')B
    on A.receiverbankaccountsha = B.receiverbankaccountsha
    group by A.receiverbankaccountsha 
    ORDER BY comment DESC, value DESC
    LIMIT 10)X
left join 
    (select identifier from fraud.aml_freshdesk_month) e 
on X.identifier = e.identifier
left join 
    (select identifier from fraud.aml_freshdesk) ee 
on X.identifier = ee.identifier
ORDER BY comment DESC, value DESC
LIMIT 5