SQLite 3.39

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