Publications Benefits Applicants

select candidates.id                                               as "ApplicantId",
       concat_ws(' ', candidates.first_name, candidates.last_name) as "ApplicantName",
       vacancies.name                                              as "PublicationName",
       vacancies.id                                                as "PublicationId",
       string_agg(benefits.name, ', ')                             as "BenefitName",
       count(benefits.id)                                          as "BenefitCount"
from candidates
         join applications on candidates.id = applications.candidates_id
         join vacancies on applications.vacancies_id = vacancies.id
         left join benefit_vacancy on vacancies.id = benefit_vacancy.vacancy_id
         left join benefits on benefit_vacancy.benefit_id = benefits.id
where candidates.date_add >= '2023-09-01'
  and candidates.date_add < '2024-01-01'
group by candidates.id, vacancies.id
order by candidates.id;