---

with parents as (
    select distinct
        si.id,
        si.name,
        si.service_type
    from ourpension.spr_indicator_bu si
    join ourpension.spr_accounts_indicator_bu sa
        on sa.spr_indicator_bu_id = si.id
    where sa.account in ('389', '390')
      and 'NPO' = any(si.service_type)
)
select
    p.id as parent_id,
    p.name as parent_name,
    p.service_type as parent_service_type,
    child.id as child_id,
    child.name as child_name,
    child.service_type as child_service_type,
    'NPO' = any(child.service_type) as child_for_npo,
    sa.account as child_account
from parents p
left join ourpension.spr_indicator_bu child
    on child.is_aggregate = p.id
left join ourpension.spr_accounts_indicator_bu sa
    on sa.spr_indicator_bu_id = child.id
order by p.id, child.id, sa.account;