Untitled

select
  ae."clientCode",
  ae."accountNumber",
  COALESCE(
    rtrim(cli.name || ' ' || cli.surname),
    rtrim(ae."clientName" || ' ' || ae."clientLastName")
  ) as "fullName",
  treward."rewardName",
  string_agg(distinct ae.reason, ',') "reason"
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" = 47
    group by
      rbr."raffleId",
      re.id
  ) treward on treward."raffleId" = ra.id
where
  ra.id = 47
  and (
    tp.id = ae."ticketProcessId"
    or ae."ticketProcessId" = 0
  )
  and ae."clientCode" = coalesce(NULL::text,ae."clientCode")
group by
	ae."clientCode",
  ae."accountNumber",
  "fullName",
  treward."rewardName"