---

select
    c.agent_id_1c,
    c.agent_id,
    a.code_1c,
    a.code,
    a.last_name,
    a.first_name,
    a.middle_name,
    count(*) as cnt
from ourpension.contract c
join ourpension.agent a
    on a.id = c.agent_id
where c.service_type in ('NPO', 'PDS')
  and c.agent_id_1c is not null
  and c.agent_id is not null
group by
    c.agent_id_1c,
    c.agent_id,
    a.code_1c,
    a.code,
    a.last_name,
    a.first_name,
    a.middle_name
order by cnt desc
limit 20;