NITIN MONTHLY FINAL

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

-- NITIN'S WALLET TOPUP RULE
SELECT '2022-11-01' as starting_date
, '2022-11-30' as ending_date
, X.active_days as active_days
, 'monthly' as date_range
, X.senderuserid as identifier 
, 'mult_cc_wallet_topup_monthly' as red_flag
, X.topupAmt as value
, X.CCused as comment
, (case when d.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	
, '2022-12-03' as run_date
FROM
    (SELECT senderuserid, CCused, topupAmt, active_days
    , row_number() OVER (ORDER BY CCused DESC, topupAmt DESC) rn
    FROM
        (SELECT A.senderuserid
        , count(distinct B.cardId) AS CCused
        , SUM(A.totaltransactionamount) AS topupAmt 
        , COUNT(DISTINCT A.updated_date) as active_days FROM
            (SELECT senderuserid, updated_date, totaltransactionamount, transaction_id
            from fraud.transaction_details_v3
            where year(updated_date) = 2022 and month(updated_date) = 12
            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 DISTINCT transaction_id, CONCAT(masked_card_number, " - " , bank_id) AS cardId
            FROM payment.transaction_payer_sources
            WHERE year = 2022 AND month = 12 AND masked_card_number IS NOT NULL)B
        ON A.transaction_id = B.transaction_id
        GROUP BY A.senderuserid
        HAVING count(distinct B.cardId) > 5)Y)X
    left join 
        (select identifier from fraud.aml_freshdesk_month UNION select identifier from fraud.aml_freshdesk) d
    on X.senderuserid = d.identifier  
WHERE rn <= 3