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,
create_date
)
SELECT 1, 'C1', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C1', 2, 1, 'reg', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C1', 3, 1, 'guest', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C2', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C2', 2, 1, 'reg', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C3', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 2, 1, 'reg', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 3, 1, 'guest', 1, DATE '2018-01-01' FROM DUAL;
9 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 | 01-JAN-22 | null | null | null | 1 | 1 | 0 |
| 1 | C2 | 2 | 1 | reg | 1 | 01-JAN-22 | null | null | null | 1 | 1 | 0 |
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 create_date >= DATE '2019-05-05'
AND create_date < DATE '2022-04-13' + INTERVAL '1' DAY
)
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 | 01-JAN-22 | null | null | null | 1 | 1 | 0 |
| 1 | C2 | 2 | 1 | reg | 1 | 01-JAN-22 | null | null | null | 1 | 1 | 0 |
| 1 | C4 | 1 | 1 | event | 1 | 01-JAN-22 | null | null | null | 1 | 1 | 0 |
| 1 | C4 | 2 | 1 | reg | 1 | 01-JAN-22 | null | null | null | 1 | 1 | 0 |
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
AND create_date >= DATE '2019-05-05'
AND create_date < DATE '2022-04-13' + INTERVAL '1' DAY;
| 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 | 01-JAN-22 | null | null | null | 1 | 1 | 0 |
| 1 | C2 | 2 | 1 | reg | 1 | 01-JAN-22 | null | null | null | 1 | 1 | 0 |