Employees with duplicate phone numbers that are cross-company

duplicate-cross-company.sql

select 
  e.phone_number, 
  e.name, 
  e.public_id as employee_public_id, 
  c.name as company_name, 
  c.public_id as company_public_id, 
  d.name as division_name, 
  d.public_id as division_public_id  
from customers_employee e 
left join customers_company c on e.company_id = c.id 
left join customers_division d on e.division_id = d.id 
where 
  is_active=true and 
  phone_number in (
    select 
      phone_number 
    from customers_employee 
    where 
      is_active=true and 
      phone_number is not null and 
      phone_number != '' 
    group by phone_number 
    having count(distinct(company_id)) > 1
  ) 
group by 
  e.phone_number, 
  e.name, 
  e.public_id, 
  c.name, 
  c.public_id, 
  d.name, 
  d.public_id 
order by 1;