Untitled

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 ticket_process tp on tp.status = 'completed'
  inner join raffle ra on ra.id = ae."raffleId"
  	and ra."deletedAt" is null
  inner join ticket_process tp2 on tp2."raffleId" = ra.id
  and tp2.status = 'completed'
  left join hist_clients cli on cli."clientCode" = ae."clientCode"
  and ra."id" = ae."raffleId"
  and cli."ticketProcessId" = tp2."id"
  inner join (
    select
      rbr."raffleId",
      string_agg(distinct re.name, '|') "rewardName"
    from
      reward re
      inner join reward_by_raffle rbr on re.id = rbr."rewardId"
    where
      rbr."raffleId" = $1
    group by
      rbr."raffleId",
      re.id
  ) treward on treward."raffleId" = ra.id
where
  ra.id = $1
  and (
    tp.id = ae."ticketProcessId"
    or ae."ticketProcessId" = 0
  )
  and ae."clientCode" = coalesce(nullif($2,''),ae."clientCode")
group by
  "ae"."clientCode",
  "fullName"