By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable(accountnumber,org_id) as (
select 4435354, 81 from dual union all
select 4435354, 281 from dual union all
select 4435354, 404 from dual union all
select 3333333, 81 from dual union all
select 3333333, 281 from dual union all
select 4444444, 81 from dual union all
select 4444444, 81 from dual union all
select 4444444, 281 from dual union all
select 4444444, 404 from dual union all
select 9999999, 81 from dual union all
select 9999999, 81 from dual union all
select 9999999, 81 from dual union all
select 9999999, 404 from dual union all
select 9999999, 404 from dual union all
select 9999999, 404 from dual union all
select 9999999, 281 from dual union all
select 9999999, 281 from dual union all
select 9999999, 281 from dual)
18 rows affected
select *
from (
select
v.*
,min(cnt_org)over(partition by accountnumber) min_cnt_org
,max(cnt_org)over(partition by accountnumber) max_cnt_org
from (
select
accountnumber
,org_id
,count(org_id) over(partition by accountnumber) cnt
,count(distinct org_id) over(partition by accountnumber) cnt_distinct
,count(*) over(partition by accountnumber,org_id) cnt_org
,listagg(org_id,',')within group(order by org_id)
over(partition by accountnumber)
as orgs
from mytable
) v
) v2
where cnt_distinct<>3
or min_cnt_org!=max_cnt_org;
| ACCOUNTNUMBER | ORG_ID | CNT | CNT_DISTINCT | CNT_ORG | ORGS | MIN_CNT_ORG | MAX_CNT_ORG |
|---|---|---|---|---|---|---|---|
| 3333333 | 81 | 2 | 2 | 1 | 81,281 | 1 | 1 |
| 3333333 | 281 | 2 | 2 | 1 | 81,281 | 1 | 1 |
| 4444444 | 81 | 4 | 3 | 2 | 81,81,281,404 | 1 | 2 |
| 4444444 | 81 | 4 | 3 | 2 | 81,81,281,404 | 1 | 2 |
| 4444444 | 281 | 4 | 3 | 1 | 81,81,281,404 | 1 | 2 |
| 4444444 | 404 | 4 | 3 | 1 | 81,81,281,404 | 1 | 2 |