Mona final monthly rule

drop table if exists fraud.shubhangi_test;
create table fraud.shubhangi_test 
AS
-- MONA'S TOPUP RULE FINAL
-- topup amt > 1000
with topup as
    (SELECT DISTINCT B.current_device_fingerprint, A.senderuserid, A.transaction_id, A.amt, A.updated_date FROM
        (SELECT senderuserid, transaction_id, totaltransactionamount as amt, updated_date
        FROM fraud.transaction_details_v3
        where updated_date BETWEEN DATE_SUB('2023-01-03',34) AND DATE_SUB('2023-01-03',4) --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'))A
    INNER JOIN
        (SELECT user_id, current_device_fingerprint, wallet_id
        FROM edw_shared.user_dimension
        WHERE wallet_id IS NOT NULL)B
    ON A.senderuserid = B.user_id)
 
-- rcvrs <= 3 AND p2ml_rcvrs > 0 
, redemption as
    (SELECT DISTINCT B.current_device_fingerprint, senderuserid, receiveruser, receiversubtype FROM
        (SELECT senderuserid, receiveruser, receiversubtype
        FROM fraud.transaction_details_v3
        WHERE updated_date BETWEEN DATE_SUB('2023-01-03',34) AND DATE_SUB('2023-01-03',4) --year(updated_date) = 2022 AND month(updated_date) = 12
        AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
        AND wallet_flag = true
        AND receiversubtype IN ('P2M_LIMITED', 'ONLINE_AGGREGATOR', 'ONLINE_MERCHANT'))A
    INNER JOIN
        (SELECT user_id, current_device_fingerprint, wallet_id
        FROM edw_shared.user_dimension
        WHERE wallet_id IS NOT NULL)B
    ON A.senderuserid = B.user_id)

SELECT DATE_SUB('2023-01-03',34) AS starting_date, DATE_SUB('2023-01-03',4) AS ending_date
, active_days
, 'monthly' as date_range
, identifier
, 'single_dev_mult_wallet_topup_monthly' as red_flag
, totTopupAmt as value
, CONCAT(walletsToppedUp, " TO ", rcvrs) AS comment
, new_or_repeat
, 'AML' AS `group`
,'FRA' AS `type`
,'Alerts' AS type_fra
,'User' AS issue_type
,'UPI' AS sub_issue_type
,'2023-01-03' as run_date FROM
    (SELECT DISTINCT A.current_device_fingerprint AS identifier, A.totTopupAmt, A.active_days, A.walletsToppedUp, A.rcvrs
    , (case when (d.identifier is null AND e.identifier is null) then 'New' else 'Repeat' end) as new_or_repeat FROM
        (SELECT current_device_fingerprint, COUNT(DISTINCT senderuserid) as walletsToppedUp, SUM(amt) as totTopupAmt, COUNT(DISTINCT updated_date) as active_days
        , rcvrs, p2ml_rcvrs FROM
            (SELECT DISTINCT A.current_device_fingerprint, A.senderuserid, A.amt, A.updated_date, A.transaction_id
            , COUNT(DISTINCT B.receiveruser) OVER(PARTITION BY A.current_device_fingerprint) AS rcvrs
            , COUNT(DISTINCT IF(B.receiversubtype = 'P2M_LIMITED', B.receiveruser, NULL)) OVER(PARTITION BY A.current_device_fingerprint) as p2ml_rcvrs
            FROM
                (SELECT * FROM topup)A
            INNER JOIN
                (SELECT * FROM redemption)B
            ON A.current_device_fingerprint = B.current_device_fingerprint AND A.senderuserid = B.senderuserid)X
        WHERE rcvrs <= 3 AND p2ml_rcvrs > 0
        GROUP BY current_device_fingerprint, rcvrs, p2ml_rcvrs
        HAVING SUM(amt) > 1000)A
    LEFT JOIN
        (select DISTINCT identifier from fraud.aml_freshdesk_month)e
    on A.current_device_fingerprint = e.identifier
    LEFT JOIN
        (select DISTINCT identifier from fraud.aml_freshdesk)d
    on A.current_device_fingerprint = d.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 < '2023-01-03')f
    On A.current_device_fingerprint = f.identifier
    LEFT JOIN 
        (select identifier from fraud.aml_banksha_temporary
        UNION select identifier from fraud.receiver_decimal_values
        UNION select identifier from fraud.sender_greater_30L
        UNION SELECT identifier FROM fraud.receiver_greater_1cr) g 
    On A.current_device_fingerprint = g.identifier
    WHERE A.current_device_fingerprint IS NOT NULL AND f.identifier IS NULL AND g.identifier IS NULL
    ORDER BY A.walletsToppedUp DESC, A.totTopupAmt DESC LIMIT 2)X
;