Как написать такой запрос в Oracle SQL?

До родных только сделал.

select r.id, r.name, s1.brothers, s2.sisters from relatives r
left join (
select parent_id, count(id) as brothers from relatives
where parent_id is not null and gender = 'М'
group by parent_id
) s1 on s1.parent_id = r.parent_id and r.gender = 'Ж'
left join (
select parent_id, count(id) as sisters from relatives
where parent_id is not null and gender = 'Ж'
group by parent_id
) s2 on s2.parent_id = r.parent_id and r.gender = 'М';

самая простая реализация такая:

with s as (select 1 id, 'Иван' name, 'м' sex, 2 parent_id from dual
union all select 2 id, 'Петр' name, 'м' sex, 3 parent_id from dual
union all select 3 id, 'Екатерина' name, 'ж' sex, null parent_id from dual
union all select 4 id, 'Мария' name, 'ж' sex, 2 parent_id from dual
union all select 5 id, 'Анастасия' name, 'ж' sex, 3 parent_id from dual
union all select 6 id, 'Павел' name, 'м' sex, 5 parent_id from dual)

select s.*,
(select count(1) from s b where b.parent_id=s.parent_id and b.id!=s.id and b.sex='м')bro,
(select count(1) from s b where b.parent_id=s.parent_id and b.id!=s.id and b.sex='ж')sis,
(select count(1) from s b where b.parent_id in (select id from s a where a.parent_id=f.parent_id) and b.id!=s.id and b.sex='м')cousm,
(select count(1) from s b where b.parent_id in (select id from s a where a.parent_id=f.parent_id) and b.id!=s.id and b.sex='ж')cousf
from s left join s f on s.parent_id=f.id
order by s.id