--MONTHLY TXN & AMT LIMITS
SELECT * FROM
(SELECT A.wallet_id, 'MIN_KYC_V2' as kyc_type, A.limit_set_on
, A.monthly_txn_limit
, IF(A.monthly_txn_limit > 0, SUM(IF(C.txns > A.monthly_txn_limit, 1, 0)), 0) AS months_monthly_txn_limit_exceeded
, IF(A.monthly_txn_limit > 0, SUM(IF(C.txns > A.monthly_txn_limit,C.txns - A.monthly_txn_limit, 0)), 0) as extra_txns
, A.monthly_amount_limit
, IF(A.monthly_amount_limit > 0, SUM(IF(C.amt > A.monthly_amount_limit, 1, 0)), 0) AS months_monthly_amt_limit_exceeded
, IF(A.monthly_amount_limit > 0, SUM(IF(C.amt > A.monthly_amount_limit, C.amt - A.monthly_amount_limit, 0)), 0) as extra_amt_transacted
FROM
(SELECT DISTINCT wallet_id, monthly_amount/100 as monthly_amount_limit, monthly_frequency AS monthly_txn_limit, DATE(updated_at) as limit_set_on
FROM wallet.wallet_limits
WHERE year = 2022 and month IN (1,2,3)
AND category = 'ORDER'
AND ((monthly_amount > 0) OR (monthly_frequency > 0)))A
INNER JOIN
(SELECT DISTINCT wallet_id
FROM wallet.wallet
WHERE wallet_state <> 'DEACTIVATED' and kyc_type = 'MIN_KYC_V2')B
ON A.wallet_id = B.wallet_id
INNER JOIN
(SELECT wallet_id, MONTH(txn_time) as txn_month, COUNT(DISTINCT txn_id) as txns, SUM(amount/100) as amt
FROM wallet.transaction_master
WHERE year = 2022 AND month IN (1,2,3)
AND category = 'ORDER' AND txn_type = 'DEBIT'
AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
GROUP BY wallet_id, MONTH(txn_time))C
ON A.wallet_id = C.wallet_id AND C.txn_month > MONTH(A.limit_set_on)
AND ((C.txns > IF(A.monthly_txn_limit > 0, A.monthly_txn_limit, 0)) OR (C.amt > IF(A.monthly_amount_limit > 0, A.monthly_amount_limit, 0)))
GROUP BY A.wallet_id, A.monthly_txn_limit, A.monthly_amount_limit, A.limit_set_on)X
WHERE (months_monthly_txn_limit_exceeded > 0) OR (extra_txns > 0) OR (months_monthly_amt_limit_exceeded > 0) OR (extra_amt_transacted > 0)
Comments