Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T (
    ID INT,
    TYPE CHAR(1),
    SEQ INT,
    GRADE CHAR(1));
INSERT INTO T VALUES (1, 'A', 1, 'I');
1 rows affected

INSERT INTO T VALUES (1, 'A', 2, 'W');
1 rows affected

INSERT INTO T VALUES (1, 'A', 3, 'W');
1 rows affected

INSERT INTO T VALUES (1, 'A', 4, 'C');
1 rows affected

INSERT INTO T VALUES (2, 'B', 1, 'W');
1 rows affected

INSERT INTO T VALUES (2, 'B', 2, 'A');
1 rows affected

INSERT INTO T VALUES (3, 'A', 1, 'I');
1 rows affected

INSERT INTO T VALUES (3, 'A', 2, 'W');
1 rows affected

SELECT 
    ID,
    TYPE,
    MAX(SEQ) SEQ,
    COUNT(*) CNT
FROM
    T 
GROUP BY
    ID,
    TYPE;


ID
TYPE
SEQ
CNT


3
A
2
2

1
A
4
4

2
B
2
2


SELECT 
    T1.ID,
    T1.TYPE,
    T1.SEQ,
    T1.GRADE,
    T2.CNT
FROM 
    T T1
JOIN
    (SELECT 
         ID,
         TYPE,
         MAX(SEQ) MAX_SEQ,
         COUNT(*) CNT
     FROM
         T
     GROUP BY
         ID,
         TYPE) T2
     ON T2.ID = T1.ID
     AND T2.TYPE = T1.TYPE
     AND T1.SEQ = T2.MAX_SEQ
WHERE
    T1.TYPE = 'A';


ID
TYPE
SEQ
GRADE
CNT


3
A
2
W
2

1
A
4
C
4