---

select
    c.number as contract_number,
    lower(btrim(i.last_name)) as last_name,
    lower(btrim(i.first_name)) as first_name,
    count(distinct c.individual_id) as individual_count,
    string_agg(
        distinct concat_ws(
            ' ',
            i.last_name,
            i.first_name,
            i.middle_name,
            '(individual_id=' || c.individual_id || ')',
            '(application_termination_contract_id=' || ct.id || ')'
        ),
        '; '
    ) as persons
from ourpension.contract c

join ourpension.individual i
    on i.id = c.individual_id

join ourpension.sharer sh
    on sh.contract_id = c.id

join ourpension.application_termination_contract_data ctd
    on sh.id = ctd.sharer_id

join ourpension.application_termination_contract ct
    on ct.id = ctd.application_termination_contract_id
   and ct.accepted is not null
   and ct.rejected is null
   and ct.canceled is null
   and ct.termination_type in ('TRANSFER', 'PAYMENT', 'FULL')

where c.individual_id is not null

group by
    c.number,
    lower(btrim(i.last_name)),
    lower(btrim(i.first_name))

having count(distinct c.individual_id) > 1

order by
    c.number,
    last_name,
    first_name;