Query to count and filter records

Question

This is my data:

ACCOUNT	PARTNER	ADDRS_NUM
HL124	2341	ASD123
HL124	2341	SDF234
HL124	234N	SDF234
HG225	45BV	QWE345
HG225	72AA	QWE345
UY150	8846	CCC204

Need to get ACCOUNT that has more than one PARTNER & ADDRS_NUM are different.

In above case, only we need to get HL124

I tried select query and then thought of filtering on the counts, but select gives weird o/p and also taking time.

SELECT
    COUNT(ACCOUNT) OVER (PARTITION BY ACCOUNT),
    COUNT(PARTNER) OVER (PARTITION BY ACCOUNT),
    COUNT(ADDRS_NUM) OVER (PARTITION BY ACCOUNT)
FROM
    table

Would like to know efficient way to get result.

Answer 1

You can use 1 window function for account and another to filter PARTNER & ADDRS_NUM multiple values -

SELECT ACCOUNT
  FROM (SELECT ACCOUNT, PARTNER, ADDRS_NUM,
               COUNT(*) OVER(PARTITION BY ACCOUNT) RN1,
               COUNT(*) OVER(PARTITION BY ACCOUNT, PARTNER ) RN2,
               COUNT(*) OVER(PARTITION BY ACCOUNT, ADDRS_NUM) RN3
          FROM DATA
       )
 WHERE RN1 > 1
   AND RN2 > 1
   AND RN3 > 1;

Demo.

Answer 2

You can use COUNT(DISTINCT ...) as follows:

SELECT 
   ACCOUNT
FROM MyData
GROUP BY ACCOUNT
HAVING 
   count(distinct PARTNER)>1
   OR
   count(distinct ADDRS_NUM)>1

This will give you one row per account if the account has either more than one partner or partner(s) in more than one address. To get the details of those rows you can combine as follows:

select *
from MyData
where ACCOUNT IN 
(SELECT 
   ACCOUNT
FROM MyData
GROUP BY ACCOUNT
HAVING 
   count(distinct PARTNER)>1
   OR
   count(distinct ADDRS_NUM)>1
)

If there are many thousands of these cases you might consider changin the IN' query to an EXISTS, or INNER JOIN` and observe change in performance.

To change the first query to 'one row per account if the account has more than one partner AND there are different addresses, you can change the OR to AND.