Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (Account_Number, Client_id, Account_Status_Code) AS
SELECT 'ABC123', 200897, 350 FROM DUAL UNION ALL
SELECT 'ABC456', 200897, 450 FROM DUAL UNION ALL
SELECT 'CDE123', 300679, 350 FROM DUAL UNION ALL
SELECT 'CDE458', 300679, 350 FROM DUAL UNION ALL
SELECT 'CDE541', 300679, 450 FROM DUAL;
5 rows affected

SELECT Account_Number,
       Client_id,
       Account_Status_Code
FROM   (
  SELECT Account_Number,
         Client_id,
         Account_Status_Code,
         COUNT(CASE account_status_code WHEN 350 THEN 1 END)
           OVER (PARTITION BY client_id) AS has350,
         COUNT(CASE account_status_code WHEN 450 THEN 1 END)
           OVER (PARTITION BY client_id) AS has450
  FROM   table_name
  WHERE  account_status_code IN (350, 450)
)
WHERE  has350 > 0
AND    has450 > 0;


ACCOUNT_NUMBER
CLIENT_ID
ACCOUNT_STATUS_CODE


ABC456
200897
450

ABC123
200897
350

CDE458
300679
350

CDE541
300679
450

CDE123
300679
350

ACCOUNT_NUMBER	CLIENT_ID	ACCOUNT_STATUS_CODE
ABC456	200897	450
ABC123	200897	350
CDE458	300679	350
CDE541	300679	450
CDE123	300679	350