Report. Process performance average status count

select
    count("candidateId") as "numCandidate",
    avg("numProcess") AS "averageProcessCount",
    avg("numCandidateAccepted") AS "averageCandidateAcceptedCount",
    avg("numCandidateRejected") AS "averageCandidateRejectedCount",
    avg("numCandidateNoReply") AS "averageCandidateNoReplyCount",
    avg("numShared") AS "averageSharedCount",
    avg("numCompanyAccepted") AS "averageCompanyAcceptedCount",
    avg("numCompanyRejected") AS "averageCompanyRejectedCount",
    avg("numCompanyNoReply") AS "averageCompanyNoReplyCount",
    avg("numInterview") AS "averageInterviewCount",
    avg("numTrialDay") AS "averageTrialDayCount",
    avg("numOffered") AS "averageOfferedCount",
    avg("numRocked") AS "averageRockedCount"
from (
         select distinct candidates.id as "candidateId",
                count(distinct process.id) as "numProcess",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '3')              as "numCandidateAccepted",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '4')              as "numCandidateRejected",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '14')             as "numCandidateNoReply",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '6')              as "numShared",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '10')             as "numCompanyAccepted",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '11')             as "numCompanyRejected",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '15')             as "numCompanyNoReply",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value in ('7', '8', '9')) as "numInterview",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '20')             as "numTrialDay",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '12')             as "numOffered",
                count(distinct process_history.row_id)
                filter (where process_history.field_name = 'status'
                    and process_history.new_value = '13')             as "numRocked"
         from candidates
                  join candidates_vacancy_requests as process on process.candidates_id = candidates.id
                  join arhistory as process_history on process_history.row_id = process.id and
                                                       process_history.table_name = 'candidates_vacancy_requests'
                  join arhistory as candidate_history on candidate_history.row_id = candidates.id and
                                                         candidate_history.table_name = 'candidates'
         where candidate_history.field_name = 'status_id'
           and candidate_history.new_value = '5'
           and not exists(select candidates_vacancy_requests.id
                          from candidates_vacancy_requests
                          where candidates_vacancy_requests.candidates_id = candidates.id
                            and candidates_vacancy_requests.status = 13
                            and date(candidates_vacancy_requests.date_update_status) >= '2022-08-22'
                            and date(candidates_vacancy_requests.date_update_status) <= '2023-08-22')
           and date(to_timestamp(process_history.created_at)) >= '2022-08-22'
           and date(to_timestamp(process_history.created_at)) <= '2023-08-22'
         group by candidates.id
     ) temp;