Oracle 21c

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

CREATE TABLE cst_cust_attributes (
  ORGANIZATION_ID NUMBER(19)    NOT NULL,
  CUST_ID         VARCHAR2(32)  NOT NULL,
  ATTRIBUTE_ID    NUMBER(19)    NOT NULL,
  ATTRIBUTE_SEQ   NUMBER(10)    NOT NULL,
  ATTRIBUTE_VALUE VARCHAR2(254) NOT NULL,
  ACTIVE_FLAG     NUMBER(3)     NOT NULL,
  CREATE_DATE     DATE          ,
  CREATE_USER     VARCHAR2(254) ,
  UPDATE_DATE     DATE          ,
 UPDATE_USER     VARCHAR2(254)
);
INSERT INTO cst_cust_attributes (
  organization_id,
  cust_id,
  attribute_id,
  attribute_seq,
  attribute_value,
  active_flag
)
SELECT 1, 'C1', 1, 1, 'event', 1 FROM DUAL UNION ALL
SELECT 1, 'C1', 2, 1, 'reg',   1 FROM DUAL UNION ALL
SELECT 1, 'C1', 3, 1, 'guest', 1 FROM DUAL UNION ALL
SELECT 1, 'C2', 1, 1, 'event', 1 FROM DUAL UNION ALL
SELECT 1, 'C2', 2, 1, 'reg',   1 FROM DUAL UNION ALL
SELECT 1, 'C3', 1, 1, 'event', 1 FROM DUAL;
6 rows affected

SELECT *
FROM   (
  SELECT a.*,
         COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_event,
         COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_reg,
         COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_guest 
  FROM   cst_cust_attributes a
)
WHERE  num_event > 0
AND    num_reg   > 0
AND    num_guest = 0;


ORGANIZATION_ID
CUST_ID
ATTRIBUTE_ID
ATTRIBUTE_SEQ
ATTRIBUTE_VALUE
ACTIVE_FLAG
CREATE_DATE
CREATE_USER
UPDATE_DATE
UPDATE_USER
NUM_EVENT
NUM_REG
NUM_GUEST


1
C2
1
1
event
1
null
null
null
null
1
1
0

1
C2
2
1
reg
1
null
null
null
null
1
1
0

ORGANIZATION_ID	CUST_ID	ATTRIBUTE_ID	ATTRIBUTE_SEQ	ATTRIBUTE_VALUE	ACTIVE_FLAG	CREATE_DATE	CREATE_USER	UPDATE_DATE	UPDATE_USER	NUM_EVENT	NUM_REG	NUM_GUEST
1	C2	1	1	event	1	null	null	null	null	1	1	0
1	C2	2	1	reg	1	null	null	null	null	1	1	0