Query to find combinations of accounts sql-CodePudding

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.

CodePudding user response：

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

db<>fiddle here

CodePudding user response：

Maybe something like this:

select ordering_account, beneficiary
from TBL_ACCOUNTS 
group by ordering_account, beneficiary
having count(*) >= 3
order by ordering_account, beneficiary

CodePudding user response：

SELECT T.ordering_account,T.beneficiary_account 
FROM TBL_ACCOUNTS T
JOIN
(
   SELECT Z.ordering_account
   FROM TBL_ACCOUNTS Z
   GROUP BY Z.ordering_account
   HAVING COUNT(*)>2
)X ON T.ordering_account=X.ordering_account
ORDER BY T.ordering_account,T.beneficiary_account

SELECT X.ordering_account,X.beneficiary_account FROM
(
  SELECT T.ordering_account,T.beneficiary_account,
   COUNT(*)OVER(PARTITION BY  T.ordering_account)XCOL
   FROM TBL_ACCOUNTS T
)X WHERE X.XCOL=3
ORDER BY X.ordering_account,X.beneficiary_account

CodePudding user response：

Self-join the table on the beneficiary account. Thus you get all ordering account pairs as often as they share the share3 beneficiary accounts. This means you can group by these pairs then and count.

The following query lists all entries of all ordering accounts for which exists another ordering account sharing at least three beneficiary accounts.

with share3 as
(
  select a1.ordering_account as acc1, a2.ordering_account as acc2
  from tbl_accounts a1
  join tbl_accounts a2 on a2.beneficiary_account = a1.beneficiary_account
                       and a2.ordering_account > a1.ordering_account
  group by a1.ordering_account, a2.ordering_account
  having count(*) >= 3
)
select *
from tbl_accounts
where exists
(
  select null
  from share3
  where share3.acc1 = tbl_accounts.ordering_account
     or share3.acc2 = tbl_accounts.ordering_account
)
order by ordering_account, beneficiary_account;

CodePudding user response：

I'm not sure I follow what you're asking, but it sounds like you simply need to include an ORDER BY clause.

At the end of your query just include

ORDER BY 'ordering account', 'beneficiary account'

The only thing that could change this is if you use different kinds of SQL that don't like single quotes. You may need to use [],"", or ``.