--DAILY TXN & AMT LIMITS
SELECT * FROM
(SELECT A.wallet_id, 'MIN_KYC_V2' as kyc_type
, A.daily_txn_limit
, IF(A.daily_txn_limit > 0, SUM(IF(C.txns > A.daily_txn_limit, 1, 0)), 0) AS days_daily_txn_limit_exceeded
, IF(A.daily_txn_limit > 0, SUM(IF(C.txns > A.daily_txn_limit,C.txns - A.daily_txn_limit, 0)), 0) as extra_txns
, A.daily_amount_limit
, IF(A.daily_amount_limit > 0, SUM(IF(C.amt > A.daily_amount_limit, 1, 0)), 0) AS days_daily_amt_limit_exceeded
, IF(A.daily_amount_limit > 0, SUM(IF(C.amt > A.daily_amount_limit, C.amt - A.daily_amount_limit, 0)), 0) as extra_amt_transacted
FROM
(SELECT DISTINCT wallet_id, daily_amount/100 as daily_amount_limit, daily_frequency AS daily_txn_limit, DATE(updated_at) as limit_set_on
FROM wallet.wallet_limits
WHERE year = 2022 and month IN (1,2,3) AND DATE(updated_at) BETWEEN '2022-01-01' AND '2022-03-31'
AND category = 'ORDER'
AND ((daily_amount > 0) OR (daily_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, Date(txn_time) as txn_date, 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, Date(txn_time))C
ON A.wallet_id = C.wallet_id AND C.txn_date >= A.limit_set_on AND ((C.txns > IF(A.daily_txn_limit > 0, A.daily_txn_limit, 0)) OR (C.amt > IF(A.daily_amount_limit > 0, A.daily_amount_limit, 0)))
GROUP BY A.wallet_id, A.daily_txn_limit, A.daily_amount_limit)X
WHERE (days_daily_txn_limit_exceeded > 0) OR (extra_txns > 0) OR (days_daily_amt_limit_exceeded > 0) OR (extra_amt_transacted > 0)
Comments