Untitled

with
  hist_clients_query as (
    select distinct
      cli."clientCode",
      cli.name,
      cli.surname
    from
      hist_clients cli
  )
select
ae."clientCode",
  COALESCE(
    rtrim(cli.name || ' ' || cli.surname),
    rtrim(ae."clientName" || ' ' || ae."clientLastName")
  ) as "fullName",
  count(distinct ae.reason) "cantidad de razones"  
from
  audit_excluded ae 
  left join hist_clients_query cli on ae."clientCode" = cli."clientCode"
where
  (
    ae."ticketProcessId" = 0
    or ae."ticketProcessId" = (
      select
        tp.id
      from
        ticket_process tp
      where
        tp.status = 'completed'
        and tp."raffleId" = ae."raffleId"
    )
  )
  and ae."raffleId" = 47
--  and ae."clientCode" = '973810'
  group by
  "ae"."clientCode",
  "fullName"