By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE transactions (`acct_sending` INTEGER, `acct_receiving` INTEGER, `amount` INTEGER, `tx_datetime` VARCHAR(19));
✓
INSERT INTO transactions (`acct_sending`, `acct_receiving`, `amount`, `tx_datetime`) VALUES
('100', '101', '10', 'yyyy-mm-dd hh-mm-ss'),
('101', '100', '5', 'yyyy-mm-dd hh-mm-ss'),
('101', '200', '1', 'yyyy-mm-dd hh-mm-ss'),
('200', '101', '11', 'yyyy-mm-dd hh-mm-ss'),
('200', '234', '22', 'yyyy-mm-dd hh-mm-ss'),
('234', '567', '24', 'yyyy-mm-dd hh-mm-ss'),
('567', '890', '56', 'yyyy-mm-dd hh-mm-ss'),
('890', '100', '73', 'yyyy-mm-dd hh-mm-ss');
✓
CREATE TABLE accounts (`account` INTEGER, `balance` INTEGER, `last_tx` VARCHAR(19));
✓
INSERT INTO accounts (`account`, `balance`, `last_tx`) VALUES
('100', '10', 'yyyy-mm-dd hh-mm-ss'),
('101', '100', 'yyyy-mm-dd hh-mm-ss'),
('102', '100', 'yyyy-mm-dd hh-mm-ss'),
('200', '1000', 'yyyy-mm-dd hh-mm-ss'),
('234', '10000', 'yyyy-mm-dd hh-mm-ss'),
('567', '1000', 'yyyy-mm-dd hh-mm-ss'),
('890', '100', 'yyyy-mm-dd hh-mm-ss');
✓
SELECT MIN(t.acct_sending, t.acct_receiving) sender,
MAX(t.acct_sending, t.acct_receiving) receiver,
COUNT(*) count
FROM transactions t
INNER JOIN accounts a1 ON a1.account = t.acct_sending AND a1.balance > 10
INNER JOIN accounts a2 ON a2.account = t.acct_receiving AND a2.balance > 10
GROUP BY sender, receiver;
| sender | receiver | count |
|---|---|---|
| 101 | 200 | 2 |
| 200 | 234 | 1 |
| 234 | 567 | 1 |
| 567 | 890 | 1 |
WITH cte AS (SELECT * FROM accounts WHERE balance > 10)
SELECT MIN(t.acct_sending, t.acct_receiving) sender,
MAX(t.acct_sending, t.acct_receiving) receiver,
COUNT(*) count
FROM transactions t
INNER JOIN cte c1 ON c1.account = t.acct_sending
INNER JOIN cte c2 ON c2.account = t.acct_receiving
GROUP BY sender, receiver;
| sender | receiver | count |
|---|---|---|
| 101 | 200 | 2 |
| 200 | 234 | 1 |
| 234 | 567 | 1 |
| 567 | 890 | 1 |