---

select
    c.number as contract_number,
    count(*) as contract_count,
    count(distinct c.individual_id) as individual_count
from ourpension.contract c
where c.individual_id is not null
group by c.number
having count(distinct c.individual_id) > 1
order by individual_count desc, c.number;



select
    c.number as contract_number,
    lower(btrim(i.last_name)) as last_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 || ')'
        ),
        '; '
    ) as persons
from ourpension.contract c
join ourpension.individual i
    on i.id = c.individual_id
where c.individual_id is not null
group by
    c.number,
    lower(btrim(i.last_name))
having count(distinct c.individual_id) > 1
order by c.number, last_name;



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 || ')'
        ),
        '; '
    ) as persons
from ourpension.contract c
join ourpension.individual i
    on i.id = c.individual_id
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;



join ourpension.individual i
    on i.id = c.individual_id
   and lower(btrim(i.last_name)) = lower(btrim(l.person_last_name))
   and lower(btrim(i.first_name)) = lower(btrim(l.person_first_name))