WALLET MERCHANT TXN CNT HOURLY /DAILY/MONTHLY

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

SELECT '2022' AS year, monthNo, kyc, catg as risk_catg
, 'Hourly' as Frequency
-- , 'Daily' as Frequency
-- , 'Monthly' as Frequency
, breaches
,'WalletMxTxnCntPerMxHourly' as rule
-- ,'WalletMxTxnCntPerMxDaily' as rule
-- ,'WalletMxTxnCntPerMxMonthly' as rule
FROM
  (SELECT Z.catg, Z.kyc, COUNT(DISTINCT Z.transaction_id) AS breaches, Z.monthNo FROM
        (SELECT A.senderuserid, A.transaction_id, month(A.TxnTime) as monthNo, COUNT(DISTINCT B.transaction_id) as txns
        , IF(C.hml_category IS NOT NULL, C.hml_category, 'LOW RISK') AS catg, D.kyc,
        CASE WHEN C.hml_category = 'HIGH RISK' THEN 5 WHEN C.hml_category = 'MEDIUM RISK' THEN 10 ELSE 15 END AS limit 
        -- CASE WHEN C.hml_category = 'HIGH RISK' THEN 10 WHEN C.hml_category = 'MEDIUM RISK' THEN 20 ELSE 25 END AS limit -- daily
        -- CASE WHEN C.hml_category = 'HIGH RISK' THEN 100 WHEN C.hml_category = 'MEDIUM RISK' THEN 150 ELSE 200 END AS limit -- monthly
        FROM
          (SELECT DISTINCT senderuserid, transaction_time as TxnTime, transaction_id
          FROM fraud.transaction_details_v3
          WHERE Year(updated_date) = 2022 
          AND workflowtype = 'CONSUMER_TO_MERCHANT' and sendertype = 'INTERNAL_USER' 
          AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
          AND wallet_flag = 'TRUE')A
        LEFT JOIN
          (SELECT DISTINCT senderuserid, transaction_time as TxnTime, transaction_id
          FROM fraud.transaction_details_v3
          WHERE Year(updated_date) = 2022 
          AND workflowtype = 'CONSUMER_TO_MERCHANT' and sendertype = 'INTERNAL_USER' 
          AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
          AND wallet_flag = 'TRUE')B
        ON A.senderuserid = B.senderuserid 
        AND ((UNIX_TIMESTAMP(A.txnTime) - UNIX_TIMESTAMP(B.txnTime))/3600) BETWEEN 0 AND 1
        -- AND ((UNIX_TIMESTAMP(A.txnTime) - UNIX_TIMESTAMP(B.txnTime))/86400) BETWEEN 0 AND 1
        -- AND ((UNIX_TIMESTAMP(A.txnTime) - UNIX_TIMESTAMP(B.txnTime))/2592000) BETWEEN 0 AND 1
        LEFT JOIN
          (SELECT DISTINCT senderuserid, hml_category
          FROM fraud.hml_classification)C
        ON A.senderuserid = C.senderuserid
        LEFT JOIN
            (SELECT DISTINCT id, IF(kyc in ('MIN_KYC', 'MIN_KYC_V2'), 'MIN', 'FULL') as kyc FROM users.users)D
        ON A.senderuserid = D.id
        GROUP BY A.senderuserid, A.transaction_id, month(A.TxnTime), C.hml_category, D.kyc)Z
  WHERE Z.txns >= Z.limit
  GROUP BY Z.catg, Z.kyc, Z.monthNo)P