--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)
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter