-- select ct.inn, count(ct.id)

-- from (select distinct c1.id as id, c1.inn as inn, coalesce(c1.personal_account, 'nopa') as pa, coalesce(c1.name, 'noname') as name

-- from rtneo_contragent c1 join rtneo_payment p1 on c1.inn = p1.inn join rtneo_contract cn1 on c1.id = cn1.contragent_id

-- where c1.delete_ts is null and p1.delete_ts is null and p1.create_ts <= '2020-07-27'::date and p1.inn is not null and c1.inn is not null) as ct

-- group by ct.name, ct.pa, ct.inn having count(ct.id)>1

--

-- select distinct c1.id as id, c1.inn as inn from rtneo_contragent c1 join rtneo_payment p1 on c1.inn = p1.inn where c1.delete_ts is null and p1.delete_ts is null order by c1.inn

select c3.personal_account, c3.name, c3.inn from (

select c2.inn as inn2, count(c2.id)

from (select distinct c1.id as id

from rtneo_contragent c1 join rtneo_payment p1 on c1.inn = p1.inn join rtneo_contract cn1 on c1.id = cn1.contragent_id

where c1.delete_ts is null and p1.delete_ts is null and p1.create_ts <= '2020-07-27'::date and p1.inn is not null and c1.inn is not null) as ct

join rtneo_contragent c2 on ct.id = c2.id

group by c2.inn having count(c2.id)>1

) as ct2 join rtneo_contragent c3 on ct2.inn2 = c3.inn and c3.personal_account is not null and c3.inn is not null and c3.delete_ts is null order by c3.inn

-- select c2.inn as inn2, count(c2.id)

-- from (select distinct c1.id as id

-- from rtneo_contragent c1 join rtneo_payment p1 on c1.inn = p1.inn join rtneo_contract cn1 on c1.id = cn1.contragent_id

-- where c1.delete_ts is null and p1.delete_ts is null and p1.create_ts <= '2020-07-27'::date and p1.inn is not null and c1.inn is not null) as ct

-- join rtneo_contragent c2 on ct.id = c2.id

-- group by c2.inn having count(c2.id)>1