Oracle 18c

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';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE PersonTable (ID, Person) AS
SELECT 1, 'name1' FROM DUAL UNION ALL
SELECT 2, 'name2' FROM DUAL UNION ALL
SELECT 3, 'name3' FROM DUAL;
3 rows affected

CREATE TABLE TypeToPersonTable (SPECID, PersonID) AS
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 8, 1 FROM DUAL UNION ALL
SELECT 10, 3 FROM DUAL;
3 rows affected

CREATE TABLE RequestSatusTable (SPECID, StatusID) AS
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 4, 0 FROM DUAL UNION ALL
SELECT 8, 0 FROM DUAL UNION ALL
SELECT 8, 1 FROM DUAL;
5 rows affected

SELECT p.Person,
       CASE r.StatusId WHEN 1 THEN 'good' ELSE 'bad' END AS status,
       COUNT(*) AS count
FROM   PersonTable p
       JOIN TypeToPersonTable t
       ON p.ID = t.PersonID
       JOIN RequestSatusTable r
       ON r.SPECID= t.SPECID
GROUP BY
       p.Person,
       r.StatusId


PERSON
STATUS
COUNT


name1
good
3

name1
bad
2