Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE contracts (agreement_id, account_number) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 5, 5 FROM DUAL;
5 rows affected

CREATE TABLE transactions (agreement_id, tran_id) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 3 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 3, 4 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL UNION ALL
SELECT 3, 6 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 4, 2 FROM DUAL UNION ALL
SELECT 4, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 4, 5 FROM DUAL UNION ALL
SELECT 4, 6 FROM DUAL UNION ALL
SELECT 4, 7 FROM DUAL UNION ALL
SELECT 5, 1 FROM DUAL UNION ALL
SELECT 5, 1 FROM DUAL;
17 rows affected

SELECT AGREEMENT_ID,
       ACCOUNT_NUMBER,
       TRAN_ID
FROM   (
  SELECT c.AGREEMENT_ID,
         c.ACCOUNT_NUMBER,
         t.TRAN_ID,
         COUNT(
           CASE WHEN t.TRAN_ID IN (2,3,4,5,6,7) THEN 1 END
         ) OVER (PARTITION BY c.AGREEMENT_ID, c.ACCOUNT_NUMBER)
           AS num_other_tarnsactions
  FROM   CONTRACTS c
         INNER JOIN TRANSACTIONS t
         ON (c.AGREEMENT_ID = t.AGREEMENT_ID)
  WHERE  t.TRAN_ID IN (1,2,3,4,5,6,7)
)
WHERE  tran_id = 1
AND    num_other_tarnsactions = 0


AGREEMENT_ID
ACCOUNT_NUMBER
TRAN_ID


1
1
1

5
5
1

5
5
1