mult_cc_wallet_topup_weekly

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

SELECT DISTINCT DATE_SUB('2023-01-13', 7) AS starting_date
,DATE_SUB('2023-01-13', 1) AS ending_date
,active_days
,'weekly' as date_range
,d.identifier AS identifier
,'mult_cc_wallet_topup_weekly' as red_flag
,topupAmt as value
,CCused as comment
,case when (ee.identifier is null AND 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
,'Wallet' as sub_issue_type
,'2023-01-13' as run_date
FROM
    (SELECT senderuserid as identifier, CCused, topupAmt, active_days
    , row_number() OVER (ORDER BY CCused DESC, topupAmt DESC) rn
    FROM
        (SELECT A.senderuserid
        , count(distinct(CONCAT(masked_card_number, " - " , card_issuer,  " - " , bank_id, " - " , sendercard_type))) AS CCused
        , SUM(A.totaltransactionamount) AS topupAmt
        , COUNT(DISTINCT updated_date) as active_days FROM
            (SELECT senderuserid, totaltransactionamount, transaction_id, card_issuer, sendercard_type, updated_date 
            from fraud.transaction_details_v3
            where updated_date BETWEEN DATE_SUB('2023-01-13', 7) AND DATE_SUB('2023-01-13', 1)
            and sendertype = 'INTERNAL_USER' AND workflowtype = 'CONSUMER_TO_MERCHANT'
            and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
            and receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') AND card_flag = true)A
        INNER JOIN
            (SELECT transaction_id, masked_card_number, bank_id
            FROM payment.transaction_payer_sources
            WHERE year IN (year(DATE_SUB('2023-01-13', 7)), year(DATE_SUB('2023-01-13', 1)))
            AND month IN (month(DATE_SUB('2023-01-13', 7)), month(DATE_SUB('2023-01-13', 1)))
            AND masked_card_number IS NOT NULL)B
        ON A.transaction_id = B.transaction_id
        GROUP BY A.senderuserid)Y)d
    left join
        (select DISTINCT identifier from fraud.aml_freshdesk WHERE run_date < DATE_SUB('2023-01-13', 7))e
    On d.identifier = e.identifier
    left join
        (select identifier from fraud.aml_freshdesk_month WHERE run_date < DATE_SUB('2023-01-13', 7)) ee
    on d.identifier = ee.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 < DATE_SUB('2023-01-13', 7))f
    On d.identifier = f.identifier
    where d.identifier is NOT NULL AND f.identifier is NULL AND rn <= 3