Query to find combinations of accounts sql

Question

I am looking for how to form a query, where I seek to find that the ordering accounts are interacting with the same beneficiary accounts 3 or more times. As I describe below.
Examples: Account A sends account 1,2,and 3. Account B sends account 1,2 and 3. Account C sends account 1,2 and 3.

This is the table called TBL_ACCOUNTS

ordering account	beneficiary account
A	1
B	1
C	1
A	2
B	2
C	2
A	3
B	3
C	3
H	1
K	23
Z	329
W	3

I want to find all those accounts that meet this condition, that the ordering accounts are interacting with the same beneficiary accounts 3 or more times. The result you would expect to get is.

ordering account	beneficiary account
A	1
A	2
A	3
B	1
B	2
B	3
C	1
C	2
C	3

I hope you can guide me which way to go, because I'm a bit lost.

Answer 1

You can create a collection data type:

CREATE TYPE int_list IS TABLE OF INT;

and then you can use:

WITH accounts (ordering_account, beneficiary_account, accounts) AS (
  SELECT t.*,
         CAST(
           COLLECT(beneficiary_account) OVER (PARTITION BY ordering_account)
           AS int_list
         )
  FROM   TBL_ACCOUNTS t
)
SELECT ordering_account,
       beneficiary_account
FROM   accounts a
WHERE  EXISTS(
         SELECT 1
         FROM   accounts x
         WHERE  a.ordering_account <> x.ordering_account
         AND    CARDINALITY(a.accounts MULTISET INTERSECT x.accounts) >= 3
         -- Remove the next line if you want to return all accounts and not just the matched accounts
         AND    a.beneficiary_account = x.beneficiary_account
       );

Which, for the sample data:

CREATE TABLE TBL_ACCOUNTS (ordering_account, beneficiary_account) AS
SELECT 'A',   1 FROM DUAL UNION ALL
SELECT 'B',   1 FROM DUAL UNION ALL 
SELECT 'C',   1 FROM DUAL UNION ALL
SELECT 'A',   2 FROM DUAL UNION ALL
SELECT 'B',   2 FROM DUAL UNION ALL
SELECT 'C',   2 FROM DUAL UNION ALL
SELECT 'A',   3 FROM DUAL UNION ALL
SELECT 'B',   3 FROM DUAL UNION ALL
SELECT 'C',   3 FROM DUAL UNION ALL
SELECT 'C',   4 FROM DUAL UNION ALL
SELECT 'H',   1 FROM DUAL UNION ALL
SELECT 'K',  23 FROM DUAL UNION ALL
SELECT 'Z', 329 FROM DUAL UNION ALL
SELECT 'W',   3 FROM DUAL;

Outputs:

ORDERING_ACCOUNT	BENEFICIARY_ACCOUNT
A	1
A	3
A	2
B	1
B	3
B	2
C	1
C	2
C	3

---

If you want to do it without a collection then:

SELECT ordering_account,
       beneficiary_account
FROM   TBL_ACCOUNTS a
WHERE  EXISTS(
         SELECT 1
         FROM   TBL_ACCOUNTS x
         WHERE  a.ordering_account <> x.ordering_account
         AND    a.beneficiary_account = x.beneficiary_account
         AND    EXISTS(
                  SELECT 1
                  FROM   TBL_ACCOUNTS l
                         INNER JOIN TBL_ACCOUNTS r
                         ON (l.beneficiary_account = r.beneficiary_account)
                  WHERE  l.ordering_account = a.ordering_account
                  AND    r.ordering_account = x.ordering_account
                  HAVING COUNT(*) >= 3
                )
       );

or:

SELECT ordering_account,
       beneficiary_account
FROM   TBL_ACCOUNTS a
WHERE  EXISTS(
         SELECT 1
         FROM   TBL_ACCOUNTS l
                INNER JOIN TBL_ACCOUNTS r
                ON (   l.beneficiary_account = r.beneficiary_account
                   AND l.ordering_account <> r.ordering_account )
         WHERE  l.ordering_account = a.ordering_account
         GROUP BY r.ordering_account
         HAVING COUNT(*) >= 3
         AND    COUNT(
                  CASE WHEN r.beneficiary_account = a.beneficiary_account THEN 1 END
                ) > 0
       );

db<>fiddle here