Category: ROCKEN Documentation

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;

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;

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;

select
    candidates.id as "candidateId",
    concat_ws(' ', candidates.first_name, candidates.last_name) as "candidateName",
       count(distinct candidates_vacancy_requests.id)   as "numTotal",
       count(distinct candidates_vacancy_requests.id)
       filter (where candidates_vacancy_requests.date_add <=
                     rocked_process.date_update_status) as "numProcessesBeforeBecomingRocked",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '3')               as "numCandidateAccepted",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '4')               as "numCandidateRejected",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '14')              as "numCandidateNoReply",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '6')               as "numShared",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '10')              as "numCompanyAccepted",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '11')              as "numCompanyRejected",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '15')              as "numCompanyNoReply",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value in ('7', '8', '9'))  as "numInterview",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '20')              as "numTrialDay",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '12')              as "numOffered",
       count(distinct arhistory.row_id)
       filter (where arhistory.field_name = 'status'
           and arhistory.new_value = '13')              as "numRocked"
from candidates
         join candidates_vacancy_requests on candidates.id = candidates_vacancy_requests.candidates_id
         join candidates_vacancy_requests as rocked_process on candidates.id = rocked_process.candidates_id
         join arhistory on arhistory.row_id = candidates_vacancy_requests.id and
                           arhistory.table_name = 'candidates_vacancy_requests'
where rocked_process.status = 13
  and date(rocked_process.date_update_status) >= '2022-08-22'
  and date(rocked_process.date_update_status) <= '2023-08-22'
group by candidates.id

brew install openvpn

sudo openvpn --config /path/to/vpn.cheitgroup.com.ovpn