Report. Rocked Candidates

select
    count(distinct candidates_vacancy_requests.id) as "ProcessTotal",
    count(distinct candidates.id) as "CandidateTotal",
       count(distinct candidates.id)
       filter (
           where (
                     candidates.residence_permits_id = 9
                         or candidates.residence_permits_id is null
                     )
           and candidates.gender = 'male'
           )
                                     as "SwissMale",
       count(distinct candidates.id)
       filter (
           where (
                     candidates.residence_permits_id = 9
                         or candidates.residence_permits_id is null
                     )
           and candidates.gender = 'female'
           )
                                     as "SwissFemale",
       count(distinct candidates.id)
       filter (
           where candidates.residence_permits_id != 9
           and candidates.gender = 'male'
           )
                                     as "ForeignMale",
       count(distinct candidates.id)
       filter (
           where candidates.residence_permits_id != 9
           and candidates.gender = 'female'
           )
                                     as "ForeignFemale"
from candidates_vacancy_requests
         join candidates on candidates.id = candidates_vacancy_requests.candidates_id
where date(candidates_vacancy_requests.date_update_status) >= '2023-01-01'
  and date(candidates_vacancy_requests.date_update_status) < '2024-01-01'
  and candidates_vacancy_requests.status = 13;