Data Exchange - Sample Queries

Jessica Dearing

February 06, 2025 21:41

Overview

The Data Exchange tool provides direct access to your high-value Evolve ATS (Jobvite) data. The Data Exchange allows you to drill directly into your data with any BI tool and create your own queries and visualizations.

To help you get started, see the sample queries from our standard reports that center on key recruiting metrics below.

Audience

Data Expert

Sample Report Queries

Candidate

Query Type	Query
Candidates	SELECT TO_CHAR(CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_sent_time", application."APP_FULL_NAME" AS "application.app_full_name", application."APP_CITY" AS "application.app_city", application."APP_STATE" AS "application.app_state", application."APP_EMAIL" AS "application.app_email", application."APP_SOURCETYPE" AS "application.app_sourcetype", application."APP_SOURCE" AS "application.app_source", application."APP_DISPOSITION_NAME" AS "application.app_disposition_name", job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_CATEGORY" AS "job.job_category", COUNT(*) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) GROUP BY CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ), 2,3,4,5,6,7,8,9,10,11,12
Candidates Activity	WITH candidate_workflow_state_sorting AS ( SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_next_workflow_state_name as pk, company_id, app_next_workflow_state_name, 0 as app_workflow_workflow_id, MIN(app_next_workflow_state_position) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(app_next_workflow_state_position), app_next_workflow_state_name ASC) as sortingkey FROM candidate_workflow GROUP BY 1,2,3 ) , candidate_workflow_prev_state_sorting AS ( SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_prev_workflow_state_name as pk, company_id, app_prev_workflow_state_name, 0 as app_workflow_workflow_id, MIN(app_prev_workflow_state_position) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(app_prev_workflow_state_position), app_prev_workflow_state_name ASC) as sortingkey FROM candidate_workflow GROUP BY 1,2,3 ) SELECT job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", application."APP_FULL_NAME" AS "application.app_full_name", application."APP_SOURCETYPE" AS "application.app_sourcetype", application."APP_SOURCE" AS "application.app_source", TO_CHAR(CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_sent_time", TO_CHAR(CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_hire_date_time", CASE WHEN lower((candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE candidate_workflow_prev_state_sorting.sortingkey END AS "candidate_workflow.app_prev_workflow_state_minimum_position", candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME" AS "candidate_workflow.app_prev_workflow_state_name", TO_CHAR(CAST(candidate_workflow."APP_PREV_WORKFLOW_STATE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MMDD HH24:MI:SS') AS "candidate_workflow.app_prev_workflow_state_time", CASE WHEN lower((candidate_workflow."APP_NEXT_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE candidate_workflow_state_sorting.sortingkey END AS "candidate_workflow.app_next_workflow_state_minimum_position", candidate_workflow."APP_NEXT_WORKFLOW_STATE_NAME" AS "candidate_workflow.app_next_workflow_state_name", TO_CHAR(CAST(candidate_workflow."APP_NEXT_WORKFLOW_STATE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MMDD HH24:MI:SS') AS "candidate_workflow.app_next_workflow_state_time", candidate_workflow."APP_WORKFLOW_STATE_IN_DAYS" AS "candidate_workflow.app_workflow_state_in_days", COUNT(DISTINCT application."APP_ID" ) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN CANDIDATE_WORKFLOW AS candidate_workflow ON (application."APP_ID") = (candidate_workflow."APP_ID") LEFT JOIN candidate_workflow_state_sorting ON(candidate_workflow."APP_NEXT_WORKFLOW_STATE_NAME")=candidate_workflow_state_sorting.app_next_wor kflow_state_name and (candidate_workflow."COMPANY_ID")=candidate_workflow_state_sorting.company_id LEFT JOIN candidate_workflow_prev_state_sorting ON (candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME")=candidate_workflow_prev_state_sorting.app_prev_w orkflow_state_name and (candidate_workflow."COMPANY_ID")=candidate_workflow_prev_state_sorting.company_id WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) GROUP BY 1,2,3,4,5,6,CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ),CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ),9,10,CAST(candidate_workflow."APP_PREV_WORKFLOW_STATE_DATE" AS TIMESTAMP_NTZ),12,13,CAST(candidate_workflow."APP_NEXT_WORKFLOW_STATE_DATE" AS TIMESTAMP_NTZ),15
Candidates and Recruiting Activity	WITH application_workflow_sorting AS (SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_workflow_state_name as company_state_pk, company_id, app_workflow_state_name, MIN(APP_WORKFLOW_STATE_POSITION) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(APP_WORKFLOW_STATE_POSITION), app_workflow_state_name ASC) as sortingkey FROM APPLICATION GROUP BY 1,2,3) , candidate_workflow_prev_state_sorting AS ( SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_prev_workflow_state_name as pk, company_id, app_prev_workflow_state_name, 0 as app_workflow_workflow_id, MIN(app_prev_workflow_state_position) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(app_prev_workflow_state_position), app_prev_workflow_state_name ASC) as sortingkey FROM candidate_workflow GROUP BY 1,2,3 ) SELECT job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", application."APP_FULL_NAME" AS "application.app_full_name", application."APP_SOURCETYPE" AS "application.app_sourcetype", application."APP_SOURCE" AS "application.app_source", TO_CHAR(CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_sent_time", TO_CHAR(CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_hire_date_time", CASE WHEN lower((candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE candidate_workflow_prev_state_sorting.sortingkey END AS "candidate_workflow.app_prev_workflow_state_minimum_position", candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME" AS "candidate_workflow.app_prev_workflow_state_name", TO_CHAR(CAST(candidate_workflow."APP_PREV_WORKFLOW_STATE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "candidate_workflow.app_prev_workflow_state_time", CASE WHEN lower((application."APP_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE application_workflow_sorting.sortingkey END AS "application.app_workflow_state_minimum_position", application."APP_WORKFLOW_STATE_NAME" AS "application.app_workflow_state_name", job."JOB_R_FULL_NAME_GROUP" AS "job.job_r_full_name_group", job."JOB_HM_FULL_NAME_GROUP" AS "job.job_hm_full_name_group", candidate_workflow."APP_WORKFLOW_STATE_IN_DAYS" AS "candidate_workflow.app_workflow_state_in_days", COUNT(DISTINCT application."APP_ID" ) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN CANDIDATE_WORKFLOW AS candidate_workflow ON (application."APP_ID") = (candidate_workflow."APP_ID") LEFT JOIN application_workflow_sorting ON (application."APP_WORKFLOW_STATE_NAME")=application_workflow_sorting.app_workflow_state_name and (application."COMPANY_ID")=application_workflow_sorting.company_id LEFT JOIN candidate_workflow_prev_state_sorting ON (candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME")=candidate_workflow_prev_state_sorting.app_prev_workflow_sta te_name and (candidate_workflow."COMPANY_ID")=candidate_workflow_prev_state_sorting.company_id WHERE ((((application."APP_MODIFIED" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_MODIFIED" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) GROUP BY 1,2,3,4,5,6,CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ),CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ),9,10,CAST(candidate_workflow."APP_PREV_WORKFLOW_STATE_DATE" AS TIMESTAMP_NTZ),12,13,14,15,16
Interview Activity	SELECT application."APP_FULL_NAME" AS "application.app_full_name", application."APP_EMAIL" AS "application.app_email", application."APP_SOURCETYPE" AS "application.app_sourcetype", application."APP_SOURCE" AS "application.app_source", company_user_interviewer."USER_FULL_NAME" AS "company_user_interviewer.user_full_name", TO_CHAR(CAST(interview."INTERVIEW_CREATED_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "interview.interview_created_time", TO_CHAR(CAST(interview."INTERVIEW_SCHEDULED_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "interview.interview_scheduled_time", interview."INTERVIEW_SUMMARY" AS "interview.interview_summary", job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", TO_CHAR(CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_hire_date_time", TO_CHAR(CAST(application."APP_START_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_start_time", COUNT(DISTINCT application."APP_ID" ) AS "application.count" FROM APPLICATION AS application LEFT JOIN INTERVIEW AS interview ON (application."APP_ID") = (interview."APP_ID") and (interview."INTERVIEW_HR_RESTRICTED")=false LEFT JOIN COMPANY_USER AS company_user_interviewer ON (company_user_interviewer."USER_ID") = (interview."USER_ID") LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") WHERE ((interview."INTERVIEW_HR_RESTRICTED")=false) AND ((((interview."INTERVIEW_SCHEDULED_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (interview."INTERVIEW_SCHEDULED_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND ((job."JOB_STATUS" IN ('Approved', 'Open'))) GROUP BY 1,2,3,4,5, CAST(interview."INTERVIEW_CREATED_DATE" AS TIMESTAMP_NTZ), CAST(interview."INTERVIEW_SCHEDULED_DATE" AS TIMESTAMP_NTZ),8,9,10,11, CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ),CAST(application."APP_START_DATE" AS TIMESTAMP_NTZ) ORDER BY 6 DESC
Offer Approvals	SELECT application."APP_FULL_NAME" AS "application.app_full_name", TO_CHAR(CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_sent_time", TO_CHAR(CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_hire_date_time", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", TO_CHAR(CAST(job."JOB_OPEN_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "job.job_open_time", company_user_offer_approver."USER_FULL_NAME" AS "company_user_offer_approver.user_full_name", approver."APPROVER_STATUS" AS "approver.approver_status", TO_CHAR(CAST(approver."APPROVER_SUBMITTED" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "approver.approver_submitted_time", TO_CHAR(CAST(approver."APPROVER_UPDATED" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "approver.approver_updated_time", approver."APPROVER_TIME_TO_UPDATE" AS "approver.approver_time_to_update", COUNT(DISTINCT application."APP_ID" ) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") LEFT JOIN APPROVER AS approver ON (application."APP_ID") = (approver."ITEM_ID") AND (approver."ITEM_KIND") = 1 INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN COMPANY_USER AS company_user_offer_approver ON (approver."USER_ID") = (company_user_offer_approver."USER_ID") WHERE ((((approver."APPROVER_UPDATED" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (approver."APPROVER_UPDATED" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) GROUP BY 1,CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ),CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ),4,5,CAST(job."JOB_OPEN_DATE" AS TIMESTAMP_NTZ),7,8,CAST(approver."APPROVER_SUBMITTED" AS TIMESTAMP_NTZ),CAST(approver."APPROVER_UPDATED" AS TIMESTAMP_NTZ),11 ORDER BY 2 DESC
Offers	SELECT application."APP_FULL_NAME" AS "application.app_full_name", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_R_FULL_NAME_GROUP" AS "job.job_r_full_name_group", job."JOB_HM_FULL_NAME_GROUP" AS "job.job_hm_full_name_group", application."APP_SOURCETYPE" AS "application.app_sourcetype", application."APP_SOURCE" AS "application.app_source", TO_CHAR(CAST(candidate_workflow."APP_NEXT_WORKFLOW_STATE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "candidate_workflow.app_next_workflow_state_time", TO_CHAR(CAST(application."APP_START_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_start_time", TO_CHAR(CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_hire_date_time", COUNT((candidate_workflow."APP_ID")) AS "candidate_workflow.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN CANDIDATE_WORKFLOW AS candidate_workflow ON (application."APP_ID") = (candidate_workflow."APP_ID") WHERE ((((candidate_workflow."APP_NEXT_WORKFLOW_STATE_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (candidate_workflow."APP_NEXT_WORKFLOW_STATE_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (((candidate_workflow."APP_NEXT_WORKFLOW_STATE_NAME") = 'Offer Sent')) GROUP BY 1,2,3,4,5,6,7,CAST(candidate_workflow."APP_NEXT_WORKFLOW_STATE_DATE" AS TIMESTAMP_NTZ),CAST(application."APP_START_DATE" AS TIMESTAMP_NTZ),CAST(application."APP_HIRE_DATE" AS TIMESTAMP_NTZ) ORDER BY 1
Pre-Interview Forms	SELECT job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", application."APP_FULL_NAME" AS "application.app_full_name", screening_form."SCREENING_FORM_NAME" AS "screening_form.screening_form_name", screening_form."SCREENING_FORM_STATUS" AS "screening_form.screening_form_status", screening_form."SCREENING_FORM_TOTAL_SCORE" AS "screening_form.screening_form_total_score", screening_form_question."SCREENING_FORM_QUESTION" AS "screening_form_question.screening_form_question", screening_form_question."SCREENING_FORM_ANSWER" AS "screening_form_question.screening_form_answer", COUNT(*) AS "screening_form_question.count" FROM SCREENING_FORM AS screening_form INNER JOIN APPLICATION AS application ON (screening_form."APP_ID") = (application."APP_ID") LEFT JOIN SCREENING_FORM_QUESTION AS screening_form_question ON (screening_form."SCREENING_FORM_ID") = (screening_form_question."SCREENING_FORM_ID") LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) GROUP BY 1,2,3,4,5,6,7,8,9 ORDER BY 10 DESC

Compliance

Query Type	Query
EEO Applicants	SELECT pplication."APP_FULL_NAME" AS "application.app_full_name", job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_LOCATION_NAME" AS "job.job_location_name", application."APP_RACE" AS "application.app_race", application."APP_GENDER" AS "application.app_gender", job."JOB_EEOCATEGORY" AS "job.job_eeocategory", application."APP_DISPOSITION_NAME" AS "application.app_disposition_name", COUNT(*) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) GROUP BY 1,2,3,4,5,6,7,8,9 ORDER BY 2
EEO Summary	SELECT application."APP_GENDER" AS "application.app_gender", application."APP_RACE" AS "application.app_race", job."JOB_EEOCATEGORY" AS "job.job_eeocategory", COUNT(*) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('month', -2, DATE_TRUNC('month', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('month', 3, DATEADD('month', -2, DATE_TRUNC('month', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))) AS TIMESTAMP_NTZ)))))) GROUP BY 1,2,3
OFCCP Applicants	SELECT job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", application."APP_FIRST_NAME" AS "application.app_first_name", application."APP_LAST_NAME" AS "application.app_last_name", application."APP_RACE" AS "application.app_race", application."APP_GENDER" AS "application.app_gender", job."JOB_JOBTYPE_NAME" AS "job.job_jobtype_name", job."JOB_EEOCATEGORY" AS "job.job_eeocategory", job."JOB_LOCATION_NAME" AS "job.job_location_name", application."APP_VETERAN_STATUS" AS "application.app_veteran_status", TO_CHAR(CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_sent_time", application."APP_SOURCETYPE" AS "application.app_sourcetype", application."APP_SOURCE" AS "application.app_source", application."APP_DISPOSITION_NAME" AS "application.app_disposition_name", compliance."COMPLIANCE_PREOFFER_GENDER" AS "compliance.compliance_preoffer_gender", compliance."COMPLIANCE_PREOFFER_ETHNICITY" AS "compliance.compliance_preoffer_ethnicity", compliance."COMPLIANCE_PREOFFER_RACE" AS "compliance.compliance_preoffer_race", compliance."COMPLIANCE_PREOFFER_DISABILITYSTATUS" AS "compliance.compliance_preoffer_disabilitystatus", CASE WHEN compliance."COMPLIANCE_PREOFFER_PROTECTEDVETERAN" THEN 'Yes' ELSE 'No' END AS "compliance.compliance_preoffer_protectedveteran", CASE WHEN compliance."COMPLIANCE_PREOFFER_DECLINETOSELFIDENTIFYVETERANSTATUS" THEN 'Yes' ELSE 'No' END AS "c.compliance_preoffer_declinetoselfidentifyveteranstatus", CASE WHEN compliance."COMPLIANCE_PREOFFER_NOTPROTECTEDVETERAN" THEN 'Yes' ELSE 'No' END AS "compliance.compliance_preoffer_notprotectedveteran", TO_CHAR(TO_DATE(CAST(compliance."COMPLIANCE_PREOFFER_FORM_SENT_DATE" AS TIMESTAMP_NTZ)), 'YYYYMM-DD') AS "compliance.compliance_preoffer_form_sent_date", compliance."COMPLIANCE_PREOFFER_FORM_STATUS" AS "compliance.compliance_preoffer_form_status", COUNT(*) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN COMPLIANCE AS compliance ON (application."APP_ID") = (compliance."APP_ID") WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) GROUP BY 1,2,3,4,5,6,7,8,9,10,11,CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ),13,14,15,16,17,18,19,20,21,22,TO_DATE(CAST(compliance."COMPLIANCE_PREOFFER_FORM_SENT_DAT E" AS TIMESTAMP_NTZ)),24 ORDER BY 1 DESC
503 (Disability) Data	SELECT compliance."COMPLIANCE_PREOFFER_DISABILITYSTATUS" AS "compliance.compliance_preoffer_disabilitystatus", job."JOB_EEOCATEGORY" AS "job.job_eeocategory", COUNT(*) AS "application.count" FROM COMPLIANCE AS compliance INNER JOIN APPLICATION AS application ON (compliance."APP_ID") = (application."APP_ID") LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") GROUP BY 1,2
VEVRAA	SELECT job."JOB_EEOCATEGORY" AS "job.job_eeocategory", SUM(CASE WHEN (compliance."COMPLIANCE_PREOFFER_PROTECTEDVETERAN") THEN 1 ELSE 0 END) AS "compliance.count_compliance_preoffer_protected_veteran", SUM(CASE WHEN (compliance."COMPLIANCE_PREOFFER_NOTPROTECTEDVETERAN") THEN 1 ELSE 0 END) AS "compliance.count_compliance_preoffer_not_protected_veteran", SUM(CASE WHEN (compliance."COMPLIANCE_PREOFFER_DECLINETOSELFIDENTIFYVETERANSTATUS") THEN 1 ELSE 0 END) AS "c.countcompliancepreofferdeclineto_self_identify_veteran_status", SUM(CASE WHEN (compliance."COMPLIANCE_POSTOFFER_PROTECTEDVETERAN") THEN 1 ELSE 0 END) AS "compliance.count_compliance_postoffer_protected_veteran", SUM(CASE WHEN (compliance."COMPLIANCE_POSTOFFER_NOTPROTECTEDVETERAN") THEN 1 ELSE 0 END) AS "compliance.count_compliance_postoffer_not_protected_veteran", SUM(CASE WHEN (compliance."COMPLIANCE_POSTOFFER_DECLINETOSELFIDENTIFYVETERANSTATUS") THEN 1 ELSE 0 END) AS "c.countcompliancepostofferdeclinetoself_identify_veteran_status", SUM(CASE WHEN (compliance."COMPLIANCE_POSTOFFER_DISABLEDVETERAN") THEN 1 ELSE 0 END) AS "compliance.count_compliance_postoffer_disabled_veteran", SUM(CASE WHEN (compliance."COMPLIANCE_POSTOFFER_RECENTLYSEPERATED") THEN 1 ELSE 0 END) AS "compliance.count_compliance_postoffer_recently_seperated", SUM(CASE WHEN (compliance."COMPLIANCE_POSTOFFER_ARMEDFORCESSERVICEMEDALVETERAN") THEN 1 ELSE 0 END) AS "c.count_compliance_postoffer_armed_forces_service_medal_veteran", SUM(CASE WHEN (compliance."COMPLIANCE_POSTOFFER_ACTIVEWARTIMEORCAMPAIGNBADGEVETERAN") THEN 1 ELSE 0 END) AS "c.countcompliancepostofferactivewartimeorcampaign_badge_veteran" FROM COMPLIANCE AS compliance INNER JOIN APPLICATION AS application ON (compliance."APP_ID") = (application."APP_ID") LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") GROUP BY 1 ORDER BY 1
EEO Form Reponses	SELECT compliance."COMPLIANCE_PREOFFER_ETHNICITY" AS "compliance.compliance_preoffer_ethnicity", compliance."COMPLIANCE_PREOFFER_GENDER" AS "compliance.compliance_preoffer_gender", compliance."COMPLIANCE_PREOFFER_RACE" AS "compliance.compliance_preoffer_race", job."JOB_EEOCATEGORY" AS "job.job_eeocategory", COUNT(*) AS "application.count" FROM COMPLIANCE AS compliance INNER JOIN APPLICATION AS application ON (compliance."APP_ID") = (application."APP_ID") LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") GROUP BY 1,2,3,4
OFCCP Search	SELECT company_user_job_searcher."USER_FULL_NAME" AS "company_user_job_searcher.user_full_name", TO_CHAR(CAST(ofccp_search_to_job_by_jobid."SEARCH_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "ofccp_search_to_job_by_jobid.search_time", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_DEPARTMENT_NAME" AS "job.job_department_name", job."JOB_LOCATION_NAME" AS "job.job_location_name", ofccp_search_to_job_by_jobid."SEARCH_KEYWORDS" AS "ofccp_search_to_job_by_jobid.search_keywords", COUNT(ofccp_search_to_job_by_jobid."SEARCH_ID" ) AS "ofccp_search_to_job_by_jobid.count" FROM JOB AS job INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN OFCCP_SEARCH AS ofccp_search_to_job_by_jobid ON (job."JOB_ID") = (ofccp_search_to_job_by_jobid."JOB_ID") LEFT JOIN COMPANY_USER AS company_user_job_searcher ON (company_user_job_searcher."USER_ID") = (ofccp_search_to_job_by_jobid."USER_ID") WHERE ((((ofccp_search_to_job_by_jobid."SEARCH_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (ofccp_search_to_job_by_jobid."SEARCH_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) GROUP BY 1,CAST(ofccp_search_to_job_by_jobid."SEARCH_DATE" AS TIMESTAMP_NTZ),3,4,5,6,7 ORDER BY 2 DESC

Engage

Query Type

Query

Contact Activity

SELECT

contact."CONTACT_FULL_NAME" AS "contact.contact_full_name",

contact_status_history."CONTACT_STATUS_HISTORY_PREVIOUS_STATUS" AS

"contact_status_history.contact_status_history_previous_status",

TO_CHAR(CAST(contact_status_history."CONTACT_STATUS_HISTORY_NEXT_STATUS_DATE" AS TIMESTAMP_NTZ),

'YYYY-MM-DD HH24:MI:SS') AS "contact_status_history.contact_status_history_next_status_time",

contact_status_history."CONTACT_STATUS_HISTORY_NEXT_STATUS" AS

"contact_status_history.contact_status_history_next_status",

TO_CHAR(CAST(contact_status_history."CONTACT_STATUS_HISTORY_PREVIOUS_STATUS_DATE" AS

TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "csh.contact_status_history_previous_status_time",

contact_status_history."CONTACT_STATUS_HISTORY_STATUS_IN_DAYS" AS

"contact_status_history.contact_status_history_status_in_days",

company_user_contact_assigned_to."USER_FULL_NAME" AS "company_user_contact_assigned_to.user_full_name",

COUNT(distinct (contact_status_history."CONTACT_ID")) AS "contact_status_history.count_contact_id",

COUNT(contact_status_history."CONTACT_STATUS_HISTORY_ID" ) AS "contact_status_history.count"

FROM CONTACT AS contact

INNER JOIN COMPANY AS company ON (contact."COMPANY_ID")=(company."COMPANY_ID")

LEFT JOIN COMPANY_USER AS company_user_contact_assigned_to ON

(contact."CONTACT_ASSIGNED_TO")=(company_user_contact_assigned_to."USER_ID")

LEFT JOIN CONTACT_STATUS_HISTORY AS contact_status_history ON (contact."CONTACT_ID") =

(contact_status_history."CONTACT_ID")

WHERE ((((contact."CONTACT_CREATED_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day',

CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(contact."CONTACT_CREATED_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

GROUP BY 1,2,CAST(contact_status_history."CONTACT_STATUS_HISTORY_NEXT_STATUS_DATE" AS

TIMESTAMP_NTZ),4,CAST(contact_status_history."CONTACT_STATUS_HISTORY_PREVIOUS_STATUS_DATE"

AS TIMESTAMP_NTZ),6,7

ORDER BY 1

Contact History

SELECT

contact_status_history."CONTACT_STATUS_HISTORY_NEXT_STATUS" AS

"contact_status_history.contact_status_history_next_status",

company_user_contact_created_by."USER_FULL_NAME" AS "company_user_contact_created_by.user_full_name",

COUNT(distinct (contact_status_history."CONTACT_ID")) AS "contact_status_history.count_contact_id"

FROM CONTACT AS contact

INNER JOIN COMPANY AS company ON (contact."COMPANY_ID")=(company."COMPANY_ID")

LEFT JOIN COMPANY_USER AS company_user_contact_created_by ON

(contact."USER_ID")=(company_user_contact_created_by."USER_ID")

LEFT JOIN CONTACT_STATUS_HISTORY AS contact_status_history ON (contact."CONTACT_ID") =

(contact_status_history."CONTACT_ID")

WHERE ((((contact."CONTACT_CREATED_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day',

CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(contact."CONTACT_CREATED_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

GROUP BY 1,2

Campaign

Details

SELECT

contact."CONTACT_FULL_NAME" AS "contact.contact_full_name",

CAST(contact."CONTACT_CREATED_DATE" AS TIMESTAMP_NTZ) AS "contact.contact_created_time",

contact."CONTACT_JOB_TITLE" AS "contact.contact_job_title",

contact."CONTACT_COMPANY_NAME" AS "contact.contact_company_name",

campaign."CAMPAIGN_NAME" AS "campaign.campaign_name",

campaign."CAMPAIGN_STATUS" AS "campaign.campaign_status",

campaign_contact."CAMPAIGN_CONTACT_STATUS" AS "campaign_contact.campaign_contact_status",

CAST(campaign."EMAIL_CAMPAIGN_LAST_SEND_DATE" AS TIMESTAMP_NTZ) AS

"campaign.email_campaign_last_send_time",

company_user_campaign_created_by."USER_FULL_NAME" AS "company_user_campaign_created_by.user_full_name",

contact."CONTACT_TAGS" AS "contact.contact_tags",

email_campaign."EMAIL_CAMPAIGN_CLICKS" AS "email_campaign.sum_email_campaign_clicks",

email_campaign."EMAIL_CAMPAIGN_UNIQUE_CLICKS" AS "email_campaign.sum_email_campaign_unique_clicks"

FROM CAMPAIGN AS campaign

LEFT JOIN CAMPAIGN_CONTACT AS campaign_contact ON (campaign."CAMPAIGN_ID") =

(campaign_contact."CAMPAIGN_ID")

LEFT JOIN CONTACT AS contact ON (campaign_contact."CONTACT_ID") = (contact."CONTACT_ID")

LEFT JOIN COMPANY_USER AS company_user_campaign_created_by ON (campaign."USER_ID") =

(company_user_campaign_created_by."USER_ID")

LEFT JOIN EMAIL_CAMPAIGN AS email_campaign ON (campaign."CAMPAIGN_ID") = (email_campaign."CAMPAIGN_ID")

INNER JOIN COMPANY AS company ON (campaign."COMPANY_ID") = (company."COMPANY_ID")

WHERE (((campaign."CAMPAIGN_TYPE") = 'Email')) AND ((((contact."CONTACT_CREATED_DATE" ) >=

((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))

AS TIMESTAMP_NTZ))) AND (contact."CONTACT_CREATED_DATE" ) < ((CAST(DATEADD('day', 30,

DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS

TIMESTAMP_NTZ))))))

ORDER BY 2 DESC

Campaign

Summary

SELECT

campaign."CAMPAIGN_NAME" AS "campaign.campaign_name",

campaign."CAMPAIGN_STATUS" AS "campaign.campaign_status",

company_user_campaign_created_by."USER_FULL_NAME" AS "company_user_campaign_created_by.user_full_name",

TO_CHAR(CAST(campaign."EMAIL_CAMPAIGN_LAST_SEND_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD

HH24:MI:SS') AS "campaign.email_campaign_last_send_time",

COALESCE(SUM((campaign."CAMPAIGN_CONTACT_COUNT") ), 0) AS "campaign.sum_campaign_contact_count",

COALESCE(SUM((email_campaign."EMAIL_CAMPAIGN_EMAILS_SENT") ), 0) AS

"email_campaign.sum_email_campaign_emails_sent",

COALESCE(SUM((email_campaign."EMAIL_CAMPAIGN_UNIQUE_CLICKS") ), 0) AS

"email_campaign.sum_email_campaign_unique_clicks",

CASE WHEN (COALESCE(SUM((email_campaign."EMAIL_CAMPAIGN_EMAILS_SENT") ), 0))=0 THEN NULL ELSE

CAST((COALESCE(SUM((email_campaign."EMAIL_CAMPAIGN_UNIQUE_CLICKS") ), 0)) as DECIMAL) /

CAST((COALESCE(SUM((email_campaign."EMAIL_CAMPAIGN_EMAILS_SENT") ), 0)) as DECIMAL) END AS

"email_campaign.measure_email_campaign_click_through_rate"

FROM CAMPAIGN AS campaign

LEFT JOIN COMPANY_USER AS company_user_campaign_created_by ON (campaign."USER_ID") =

(company_user_campaign_created_by."USER_ID")

LEFT JOIN EMAIL_CAMPAIGN AS email_campaign ON (campaign."CAMPAIGN_ID") = (email_campaign."CAMPAIGN_ID")

INNER JOIN COMPANY AS company ON (campaign."COMPANY_ID") = (company."COMPANY_ID")

WHERE ((((campaign."CAMPAIGN_CREATED_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day',

CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(campaign."CAMPAIGN_CREATED_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

AND

(((campaign."CAMPAIGN_TYPE") = 'Email'))

GROUP BY 1,2,3,CAST(campaign."EMAIL_CAMPAIGN_LAST_SEND_DATE" AS TIMESTAMP_NTZ)

ORDER BY 1

Engage User Activity

SELECT

company_user."USER_FULL_NAME" AS "company_user.user_full_name",

CASE WHEN engage_user_activity."USER_PUBLISHER_STATUS" THEN 'Yes' ELSE 'No' END

AS "engage_user_activity.user_publisher_status",

COALESCE(SUM((engage_user_activity."USER_MESSAGES_SENT_TO_CONTACTS") ), 0) AS

"engage_user_activity.user_messages_sent_to_contacts_sum",

COALESCE(SUM((engage_user_activity."USER_SHARE_CONTACT_COUNT") ), 0) AS

"engage_user_activity.user_share_contact_count_sum",

COALESCE(SUM((engage_user_activity."USER_REQUISITION_COUNT") ), 0) AS

"engage_user_activity.user_requisition_count_sum",

COALESCE(SUM((engage_user_activity."USER_ASSIGNED_CONTACTS") ), 0) AS

"engage_user_activity.user_assigned_contacts_sum",

COALESCE(SUM((engage_user_activity."USER_ACTIVE_CAMPAIGNS") ), 0) AS

"engage_user_activity.user_active_campaigns_sum",

COALESCE(SUM((engage_user_activity."USER_ACTIVE_EMAIL_CAMPAIGNS") ), 0) AS

"engage_user_activity.user_active_email_campaigns_sum",

COALESCE(SUM((engage_user_activity."USER_ACTIVE_SOCIAL_CAMPAIGNS") ), 0) AS

"engage_user_activity.user_active_social_campaigns_sum",

COALESCE(SUM((engage_user_activity."USER_ACTIVE_WEBPAGE_CAMPAIGNS") ), 0) AS

"engage_user_activity.user_active_webpage_campaigns_sum",

COALESCE(SUM((engage_user_activity."USER_CONTACTS_MOVED_CONTACTED") ), 0) AS

"engage_user_activity.user_contacts_moved_contacted_sum",

COALESCE(SUM((engage_user_activity."USER_CANDIDATES_ADDED") ), 0) AS

"engage_user_activity.user_candidates_added_sum",

COALESCE(SUM((engage_user_activity."USER_CONTACTS_MOVED_HIRED") ), 0) AS

"engage_user_activity.user_contacts_moved_hired_sum",

COALESCE(SUM((engage_user_activity."USER_CONTACTS_EVER_MOVED_CONTACTED_STATUS") ), 0) AS

"eua.user_contacts_ever_moved_contacted_status_sum",

COALESCE(SUM((engage_user_activity."USER_CONTACTS_EVER_MOVED_CANDIDATE_STATUS") ), 0) AS

"eua.user_contacts_ever_moved_candidate_status_sum",

COALESCE(SUM((engage_user_activity."USER_CONTACTS_EVER_MOVED_HIRED_STATUS") ), 0) AS

"engage_user_activity.user_contacts_ever_moved_hired_status_sum",

COALESCE(SUM((engage_user_activity."USER_SOCIAL_CAMPAIGN_MESSAGES_SENT") ), 0) AS

"engage_user_activity.user_social_campaign_messages_sent_sum",

COALESCE(SUM((engage_user_activity."USER_EMAIL_CAMPAIGN_MESSAGES_SENT") ), 0) AS

"engage_user_activity.user_email_campaign_messages_sent_sum",

COALESCE(SUM((engage_user_activity."USER_TASK_CREATED") ), 0) AS

"engage_user_activity.user_task_created_sum",

COALESCE(SUM((engage_user_activity."USER_TASK_COMPLETED") ), 0) AS

"engage_user_activity.user_task_completed_sum",

COALESCE(SUM((engage_user_activity."USER_TASK_INCOMPLETE") ), 0) AS

"engage_user_activity.user_task_incomplete_sum",

COALESCE(SUM((engage_user_activity."USER_TASK_OVERDUE") ), 0) AS

"engage_user_activity.user_task_overdue_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_RECEIVE") ), 0) AS

"engage_user_activity.user_jobvite_receive_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_SEND") ), 0) AS "engage_user_activity.user_jobvite_send_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_BROADCAST") ), 0) AS

"engage_user_activity.user_jobvite_broadcast_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_VIEW") ), 0) AS "engage_user_activity.user_jobvite_view_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_CLICK_TO_APPLY") ), 0) AS

"engage_user_activity.user_jobvite_click_to_apply_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_FORWARD") ), 0) AS

"engage_user_activity.user_jobvite_forward_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_APPLY") ), 0) AS

"engage_user_activity.user_jobvite_apply_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_HIRE") ), 0) AS "engage_user_activity.user_jobvite_hire_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_EMAIL_DIRECT") ), 0) AS

"engage_user_activity.user_jobvite_email_direct_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_LINKEDIN_DIRECT") ), 0) AS

"engage_user_activity.user_jobvite_linkedin_direct_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_LINKEDIN_BROADCAST") ), 0) AS

"engage_user_activity.user_jobvite_linkedin_broadcast_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_FACEBOOK_BROADCAST") ), 0) AS

"engage_user_activity.user_jobvite_facebook_broadcast_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_TWITTER_BROADCAST") ), 0) AS

"engage_user_activity.user_jobvite_twitter_broadcast_sum",

COALESCE(SUM((engage_user_activity."USER_JOBVITE_JOBLINK_BROADCAST") ), 0) AS

"engage_user_activity.user_jobvite_joblink_broadcast_sum",

COALESCE(SUM((engage_user_activity."USER_CONTACTS_ADDED") ), 0) AS

"engage_user_activity.user_contacts_added_sum",

COALESCE(SUM((engage_user_activity."USER_CONTACTS_MOVED") ), 0) AS

"engage_user_activity.user_contacts_moved_sum",

COALESCE(SUM((engage_user_activity."USER_NOTES_ADDED") ), 0) AS "engage_user_activity.user_notes_added_sum"

FROM COMPANY_USER AS company_user

LEFT JOIN ENGAGE_USER_ACTIVITY AS engage_user_activity ON

(company_user."USER_ID")=(engage_user_activity."USER_ID")

WHERE ((((engage_user_activity."USER_ACTIVITY_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day',

CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(engage_user_activity."USER_ACTIVITY_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

GROUP BY 1,2

ORDER BY 1

Pipeline Contact Details

SELECT

contact."CONTACT_FULL_NAME" AS "contact.contact_full_name",

TO_CHAR(CAST(contact."CONTACT_CREATED_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS

"contact.contact_created_time",

TO_CHAR(CAST(contact."CONTACT_MODIFIED_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS

"contact.contact_modified_time",

contact."CONTACT_ADDRESS1" AS "contact.contact_address1",

contact."CONTACT_CITY" AS "contact.contact_city",

contact."CONTACT_STATE" AS "contact.contact_state",

contact."CONTACT_COUNTRY" AS "contact.contact_country",

contact."CONTACT_PRIMARY_EMAIL" AS "contact.contact_primary_email",

contact."CONTACT_JOB_TITLE" AS "contact.contact_job_title",

contact."CONTACT_COMPANY_NAME" AS "contact.contact_company_name",

contact."CONTACT_SOURCE_NAME" AS "contact.contact_source_name",

contact."CONTACT_TAGS" AS "contact.contact_tags",

contact."CONTACT_NOTES" AS "contact.contact_notes",

COUNT(*) AS "contact.count"

FROM CONTACT AS contact

INNER JOIN COMPANY AS company ON (contact."COMPANY_ID")=(company."COMPANY_ID")

WHERE ((((contact."CONTACT_CREATED_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day',

CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(contact."CONTACT_CREATED_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

GROUP BY 1,CAST(contact."CONTACT_CREATED_DATE" AS TIMESTAMP_NTZ),

CAST(contact."CONTACT_MODIFIED_DATE" AS TIMESTAMP_NTZ),4,5,6,7,8,9,10,11,12,13

ORDER BY 2 DESC

Social Media Campaigns

SELECT

campaign."CAMPAIGN_NAME" AS "campaign.campaign_name",

campaign."CAMPAIGN_STATUS" AS "campaign.campaign_status",

company_user_campaign_created_by."USER_FULL_NAME" AS "company_user_campaign_created_by.user_full_name",

TO_CHAR(CAST(campaign."SOCIAL_CAMPAIGN_LAST_SEND_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD

HH24:MI:SS') AS "campaign.social_campaign_last_send_time",

COALESCE(SUM((social_media_campaign."SOCIAL_CAMPAIGN_MESSAGE_SENT") ), 0) AS

"social_media_campaign.sum_social_campaign_message_sent",

COALESCE(SUM((social_media_campaign."SOCIAL_CAMPAIGN_CLICKS") ), 0) AS

"social_media_campaign.sum_social_campaign_clicks"

FROM CAMPAIGN AS campaign

LEFT JOIN COMPANY_USER AS company_user_campaign_created_by ON (campaign."USER_ID") =

(company_user_campaign_created_by."USER_ID")

LEFT JOIN SOCIAL_MEDIA_CAMPAIGN AS social_media_campaign ON (campaign."CAMPAIGN_ID") =

(social_media_campaign."CAMPAIGN_ID")

INNER JOIN COMPANY AS company ON (campaign."COMPANY_ID") = (company."COMPANY_ID")

WHERE ((((campaign."CAMPAIGN_CREATED_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day',

CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(campaign."CAMPAIGN_CREATED_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

AND (((campaign."CAMPAIGN_TYPE") = 'Social'))

GROUP BY 1,2,3,CAST(campaign."SOCIAL_CAMPAIGN_LAST_SEND_DATE" AS TIMESTAMP_NTZ)

ORDER BY 3 DESC

Web Page Campaigns

SELECT

campaign."CAMPAIGN_NAME" AS "campaign.campaign_name",

campaign."CAMPAIGN_STATUS" AS "campaign.campaign_status",

campaign."WEBPAGE_CAMPAIGN_URL" AS "campaign.webpage_campaign_url",

company_user_campaign_created_by."USER_FULL_NAME" AS "company_user_campaign_created_by.user_full_name",

COALESCE(SUM((webpage_campaign."WEBPAGE_CAMPAIGN_VIEWS")), 0) AS

"webpage_campaign.sum_webpage_campaign_views",

COALESCE(SUM((webpage_campaign."WEBPAGE_CAMPAIGN_APPLIES")), 0) AS

"webpage_campaign.sum_webpage_campaign_applies",

COALESCE(SUM((webpage_campaign."WEBPAGE_CAMPAIGN_UNIQUE_CLICKS") ), 0) AS

"webpage_campaign.sum_webpage_campaign_unique_clicks",

CASE WHEN (COALESCE(SUM((webpage_campaign."WEBPAGE_CAMPAIGN_VIEWS")), 0))=0 THEN NULL ELSE

CAST((COALESCE(SUM((webpage_campaign."WEBPAGE_CAMPAIGN_UNIQUE_CLICKS") ), 0)) as

DECIMAL) / CAST((COALESCE(SUM((webpage_campaign."WEBPAGE_CAMPAIGN_VIEWS")), 0)) as

DECIMAL) END AS "webpage_campaign.click_through_rate"

FROM CAMPAIGN AS campaign

LEFT JOIN WEBPAGE_CAMPAIGN AS webpage_campaign ON (campaign."CAMPAIGN_ID") =

(webpage_campaign."CAMPAIGN_ID")

LEFT JOIN COMPANY_USER AS company_user_campaign_created_by ON (campaign."USER_ID") =

(company_user_campaign_created_by."USER_ID")

INNER JOIN COMPANY AS company ON (campaign."COMPANY_ID") = (company."COMPANY_ID")

WHERE ((((campaign."CAMPAIGN_CREATED_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day',

CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(campaign."CAMPAIGN_CREATED_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

AND (((campaign."CAMPAIGN_TYPE") = 'Website'))

GROUP BY 1,2,3,4

ORDER BY 1

Requisitions

Query Type	Query
Agency	SELECT TO_CHAR(CAST(agency_job."AGENCY_JOB_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "agency_job.agency_job_sent_time", agency_user."AGENCY_FULL_NAME" AS "agency_user.agency_full_name", agency_user."AGENCY_EMAIL" AS "agency_user.agency_email", job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", TO_CHAR(CAST(job."JOB_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "job.job_sent_time", job."JOB_STATUS" AS "job.job_status", COUNT(*) AS "job.count" FROM JOB AS job LEFT JOIN AGENCY_JOB AS agency_job ON (agency_job."JOB_ID") = (job."JOB_ID") LEFT JOIN AGENCY_USER AS agency_user ON (agency_job."USER_ID") = (agency_user."AGENCY_USER_ID") INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((job."JOB_OPEN_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (job."JOB_OPEN_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (CASE WHEN agency_job_id IS NOT NULL THEN True ELSE False END) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY CAST(agency_job."AGENCY_JOB_SENT_DATE" AS TIMESTAMP_NTZ),2,3,4,5,6,CAST(job."JOB_SENT_DATE" AS TIMESTAMP_NTZ),8 ORDER BY 2 ,7
Hiring Summary	SELECT job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_DEPARTMENT_NAME" AS "job.job_department_name", job."JOB_R_FULL_NAME_GROUP" AS "job.job_r_full_name_group", job."JOB_HM_FULL_NAME_GROUP" AS "job.job_hm_full_name_group", company_user_requisition_approver."USER_FULL_NAME" AS "company_user_requisition_approver.user_full_name", job."JOB_STATUS" AS "job.job_status", COUNT(DISTINCT job."JOB_ID" ) AS "job.count", COALESCE(COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE((job."JOB_OPENINGS"),0)(10000001.0)) AS DECIMAL(38,0))) + (TO_NUMBER(job."JOB_ID" ) % 1.0e27)::NUMERIC(38, 0) ) - SUM(DISTINCT (TO_NUMBER(job."JOB_ID" ) % 1.0e27)::NUMERIC(38, 0)) ) AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0), 0) AS "job.sum_job_openings" FROM JOB AS job LEFT JOIN APPROVER AS approver ON (approver."ITEM_ID") = (job."JOB_ID") and (approver."ITEM_KIND")=0 LEFT JOIN COMPANY_USER AS company_user_requisition_approver ON (approver."USER_ID") = (company_user_requisition_approver."USER_ID") INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") WHERE (((job."JOB_STATUS") = 'Open')) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1,2,3,4,5,6,7,8 ORDER BY 1
Pipeline	WITH current_candidates_in_workflow_state AS (WITH application_workflow_sorting AS (SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_workflow_state_name as company_state_pk, company_id, app_workflow_state_name, MIN(APP_WORKFLOW_STATE_POSITION) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(APP_WORKFLOW_STATE_POSITION), app_workflow_state_name ASC) as sortingkey FROM APPLICATION GROUP BY 1,2,3) SELECT application."JOB_ID" AS job_id, CASE WHEN lower((application."APP_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE application_workflow_sorting.sortingkey END AS "application.app_workflow_state_minimum_position", application."APP_WORKFLOW_STATE_NAME" AS app_workflow_state_name, company."COMPANY_ID" AS company_id, COUNT(*) AS current_candidates_in_workflow_state FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN application_workflow_sorting ON (application."APP_WORKFLOW_STATE_NAME")=application_workflow_sorting.app_workflow_state_name and (application."COMPANY_ID")=application_workflow_sorting.company_id WHERE (job."JOB_PRIVATE") = false AND (application."JOB_ID") <> 0 GROUP BY 1,2,3,4) SELECT COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_REQUISITION_ID" AS "job.job_requisition_id", job."JOB_LOCATION_NAME" AS "job.job_location_name", job."JOB_HM_FULL_NAME_GROUP" AS "job.job_hm_full_name_group", job."JOB_R_FULL_NAME_GROUP" AS "job.job_r_full_name_group", job."JOB_TIME_TO_FILL" AS "job.job_time_to_fill", current_candidates_in_workflow_state.app_workflow_state_name AS "current_candidates_in_workflow_state.app_workflow_state_name", COALESCE(SUM(current_candidates_in_workflow_state.current_candidates_in_workflow_state ), 0) AS "cciws.count_current_candidates_in_workflow_state" FROM JOB AS job INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN current_candidates_in_workflow_state ON (job."JOB_ID") = current_candidates_in_workflow_state.job_id WHERE (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1,2,3,4,5,6,7,8 ORDER BY 1
Pipeline Details	WITH application_workflow_sorting AS (SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_workflow_state_name as company_state_pk, company_id, app_workflow_state_name, MIN(APP_WORKFLOW_STATE_POSITION) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(APP_WORKFLOW_STATE_POSITION), app_workflow_state_name ASC) as sortingkey FROM APPLICATION GROUP BY 1,2,3) SELECT COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_REQUISITION_ID" AS "job.job_requisition_id", job."JOB_STATUS" AS "job.job_status", job."JOB_OPENINGS" AS "job.job_openings", job."JOB_DEPARTMENT_NAME" AS "job.job_department_name", job."JOB_WORKFLOW_TITLE" AS "job.job_workflow_title", job."JOB_LOCATION_NAME" AS "job.job_location_name", TO_CHAR(CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "application.app_sent_time", application."APP_SOURCETYPE" AS "application.app_sourcetype", application."APP_SOURCE" AS "application.app_source", application."APP_FULL_NAME" AS "application.app_full_name", CASE WHEN lower((application."APP_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE application_workflow_sorting.sortingkey END AS "application.app_workflow_state_minimum_position", application."APP_WORKFLOW_STATE_NAME" AS "application.app_workflow_state_name", CASE WHEN (COUNT(application."APP_ID" ))=0 THEN NULL ELSE CAST((COALESCE(SUM((application."APP_WORKFLOW_STATE_IN_DAYS") ), 0)) as decimal)/cast((COUNT(application."APP_ID" )) as decimal) END AS "application.avg_app_workflow_state_in_days" FROM JOB AS job LEFT JOIN APPLICATION AS application ON (application."JOB_ID") = (job."JOB_ID") LEFT JOIN application_workflow_sorting ON (application."APP_WORKFLOW_STATE_NAME")=application_workflow_sorting.app_workflow_state_name and (application."COMPANY_ID")=application_workflow_sorting.company_id INNER JOIN JOBVITE_DWH.COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") WHERE (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1,2,3,4,5,6,7,8,CAST(application."APP_SENT_DATE" AS TIMESTAMP_NTZ),10,11,12,13,14 ORDER BY 1
Pipeline History	WITH candidate_workflow_state_sorting AS ( SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_next_workflow_state_name as pk, company_id, app_next_workflow_state_name, 0 as app_workflow_workflow_id, MIN(app_next_workflow_state_position) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(app_next_workflow_state_position), app_next_workflow_state_name ASC) as sortingkey FROM candidate_workflow GROUP BY 1,2,3 ) SELECT candidate_workflow."APP_NEXT_WORKFLOW_STATE_NAME" AS "candidate_workflow.app_next_workflow_state_name", CASE WHEN lower((candidate_workflow."APP_NEXT_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE candidate_workflow_state_sorting.sortingkey END AS "candidate_workflow.app_next_workflow_state_minimum_position", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", application."APP_SOURCETYPE" AS "application.app_sourcetype", COUNT(DISTINCT application."APP_ID" ) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN CANDIDATE_WORKFLOW AS candidate_workflow ON (application."APP_ID") = (candidate_workflow."APP_ID") LEFT JOIN candidate_workflow_state_sorting ON (candidate_workflow."APP_NEXT_WORKFLOW_STATE_NAME")= candidate_workflow_state_sorting.app_next_workflow_state_name and (candidate_workflow."COMPANY_ID")=candidate_workflow_state_sorting.company_id WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (job."JOB_PRIVATE") = false AND (application."JOB_ID") <> 0 GROUP BY 1,2,3,4,5
Pipeline Summary	WITH application_workflow_sorting AS (SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_workflow_state_name as company_state_pk, company_id, app_workflow_state_name, MIN(APP_WORKFLOW_STATE_POSITION) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(APP_WORKFLOW_STATE_POSITION), app_workflow_state_name ASC) as sortingkey FROM APPLICATION GROUP BY 1,2,3) SELECT application."APP_WORKFLOW_STATE_NAME" AS "application.app_workflow_state_name", CASE WHEN lower((application."APP_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE application_workflow_sorting.sortingkey END AS "application.app_workflow_state_minimum_position", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", application."APP_SOURCETYPE" AS "application.app_sourcetype", COUNT(*) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN application_workflow_sorting ON (application."APP_WORKFLOW_STATE_NAME")=application_workflow_sorting.app_workflow_state_name and (application."COMPANY_ID")=application_workflow_sorting.company_id WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (job."JOB_PRIVATE") = false AND (application."JOB_ID") <> 0 GROUP BY 1,2,3,4,5
Requisition Approvals	SELECT COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_DEPARTMENT_NAME" AS "job.job_department_name", job."JOB_OPENINGS" AS "job.job_openings", job."JOB_STATUS" AS "job.job_status", TO_CHAR(CAST(job."JOB_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "job.job_sent_time", company_user_requisition_approver."USER_FULL_NAME" AS "company_user_requisition_approver.user_full_name", approver."APPROVER_STATUS" AS "approver.approver_status", TO_CHAR(CAST(approver."APPROVER_SUBMITTED" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "approver.approver_submitted_time", TO_CHAR(CAST(approver."APPROVER_UPDATED" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "approver.approver_updated_time", COUNT(DISTINCT job."JOB_ID" ) AS "job.count", CASE WHEN (COUNT(DISTINCT job."JOB_ID" )) = 0 THEN NULL ELSE cast((COALESCE(COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE((job."JOB_TIME_TO_APPROVE"),0)(10000001.0)) AS DECIMAL(38,0))) + (TO_NUMBER(job."JOB_ID" ) % 1.0e27)::NUMERIC(38, 0) ) - SUM(DISTINCT (TO_NUMBER(job."JOB_ID" ) % 1.0e27)::NUMERIC(38, 0)) ) AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0), 0)) as decimal)/cast((COUNT(DISTINCT job."JOB_ID" )) as decimal) END AS "job.avg_job_time_to_approve" FROM JOB AS job LEFT JOIN APPROVER AS approver ON (approver."ITEM_ID") = (job."JOB_ID") and (approver."ITEM_KIND")=0 LEFT JOIN COMPANY_USER AS company_user_requisition_approver ON (approver."USER_ID") = (company_user_requisition_approver."USER_ID") INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((job."JOB_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (job."JOB_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (((job."JOB_STATUS") = 'Approved')) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1,2,3,4,5,CAST(job."JOB_SENT_DATE" AS TIMESTAMP_NTZ),7,8,CAST(approver."APPROVER_SUBMITTED" AS TIMESTAMP_NTZ),CAST(approver."APPROVER_UPDATED" AS TIMESTAMP_NTZ) ORDER BY 7 DESC
Requisitions	SELECT job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_DEPARTMENT_NAME" AS "job.job_department_name", job."JOB_R_FULL_NAME_GROUP" AS "job.job_r_full_name_group", job."JOB_HM_FULL_NAME_GROUP" AS "job.job_hm_full_name_group", company_user_requisition_approver."USER_FULL_NAME" AS "company_user_requisition_approver.user_full_name", job."JOB_STATUS" AS "job.job_status", COUNT(DISTINCT job."JOB_ID" ) AS "job.count", COALESCE(COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE((job."JOB_OPENINGS"),0)(10000001.0)) AS DECIMAL(38,0))) + (TO_NUMBER(job."JOB_ID" ) % 1.0e27)::NUMERIC(38, 0) ) - SUM(DISTINCT (TO_NUMBER(job."JOB_ID" ) % 1.0e27)::NUMERIC(38, 0)) ) AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0), 0) AS "job.sum_job_openings" FROM JOB AS job LEFT JOIN APPROVER AS approver ON (approver."ITEM_ID") = (job."JOB_ID") and (approver."ITEM_KIND")=0 LEFT JOIN COMPANY_USER AS company_user_requisition_approver ON (approver."USER_ID") = (company_user_requisition_approver."USER_ID") INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((job."JOB_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (job."JOB_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (((job."JOB_STATUS") = 'Open')) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1,2,3,4,5,6,7,8 ORDER BY 1
Requisition Summary	SELECT job."JOB_STATUS" AS "job.job_status", COUNT(*) AS "job.count" FROM JOB AS job INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((job."JOB_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (job."JOB_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1 ORDER BY 1
Time To Fill	SELECT job."JOB_R_FULL_NAME_GROUP" AS "job.job_r_full_name_group", job."JOB_HM_FULL_NAME_GROUP" AS "job.job_hm_full_name_group", job."JOB_REQUISITION_ID" AS "job.job_requisition_id", COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title", job."JOB_EID" AS "job.job_eid", job."JOB_DEPARTMENT_NAME" AS "job.job_department_name", TO_CHAR(CAST(job."JOB_OPEN_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "job.job_open_time", job."JOB_STATUS" AS "job.job_status", TO_CHAR(CAST(job."JOB_FILLED_ON" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "job.job_filled_time", TO_CHAR(CAST(job."JOB_MODIFIED_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "job.job_modified_time", job."JOB_HOLD_TIME_SPAN" AS "job.job_hold_time_span", job."JOB_DAYS_OPEN" AS "job.job_days_open", TO_CHAR(CAST(job."JOB_CLOSE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "job.job_close_time", AVG((job."JOB_TIME_TO_FILL")) AS "job.avg_job_time_to_fill" FROM JOB AS job INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((job."JOB_CLOSE_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (job."JOB_CLOSE_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND ((job."JOB_STATUS" IN ('Closed', 'Filled'))) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1,2,3,4,5,6,CAST(job."JOB_OPEN_DATE" AS TIMESTAMP_NTZ),8,CAST(job."JOB_FILLED_ON" AS TIMESTAMP_NTZ),CAST(job."JOB_MODIFIED_DATE" AS TIMESTAMP_NTZ),11,12,CAST(job."JOB_CLOSE_DATE" AS TIMESTAMP_NTZ) ORDER BY 6 DESC

Sourcing

Query Type

Query

Employee Jobvites

SELECT

company_user."USER_FULL_NAME" AS "company_user.user_full_name",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_RECEIVE") ), 0) AS

"user_jobvite_activity.user_jobvite_receive_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_SEND") ), 0) AS "user_jobvite_activity.user_jobvite_send_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_BROADCAST") ), 0) AS

"user_jobvite_activity.user_jobvite_broadcast_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_VIEW") ), 0) AS "user_jobvite_activity.user_jobvite_view_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_CLICK_TO_APPLY") ), 0) AS

"user_jobvite_activity.user_jobvite_click_to_apply_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_FORWARD") ), 0) AS

"user_jobvite_activity.user_jobvite_forward_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_APPLY") ), 0) AS "user_jobvite_activity.user_jobvite_apply_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_HIRE")), 0) AS "user_jobvite_activity.user_jobvite_hire_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_EMAIL_DIRECT") ), 0) AS

"user_jobvite_activity.user_jobvite_email_direct_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_LINKEDIN_DIRECT") ), 0) AS

"user_jobvite_activity.user_jobvite_linkedin_direct_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_LINKEDIN_BROADCAST") ), 0) AS

"user_jobvite_activity.user_jobvite_linkedin_broadcast_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_FACEBOOK_BROADCAST") ), 0) AS

"user_jobvite_activity.user_jobvite_facebook_broadcast_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_TWITTER_BROADCAST") ), 0) AS

"user_jobvite_activity.user_jobvite_twitter_broadcast_sum",

COALESCE(SUM((user_jobvite_activity."USER_JOBVITE_JOBLINK_BROADCAST") ), 0) AS

"user_jobvite_activity.user_jobvite_joblink_broadcast_sum"

FROM JOBVITE_DWH.COMPANY_USER AS company_user

LEFT JOIN JOBVITE_DWH.USER_JOBVITE_ACTIVITY AS user_jobvite_activity ON

(company_user."USER_ID")=(user_jobvite_activity."USER_ID")

WHERE ((((user_jobvite_activity."USER_JOBVITE_ACTIVITY_DATE" ) >= ((CAST(DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(user_jobvite_activity."USER_JOBVITE_ACTIVITY_DATE" ) < (( CAST(DATEADD('day', 30, DATEADD('day', -

29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

GROUP BY 1

ORDER BY 1

Jobvites

SELECT

COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title",

job."JOB_EID" AS "job.job_eid",

job."JOB_REQUISITION_ID" AS "job.job_requisition_id",

job."JOB_CATEGORY" AS "job.job_category",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_SEND") ), 0) AS "job_jobvite_activity.job_jobvite_send_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_BROADCAST") ), 0) AS

"job_jobvite_activity.job_jobvite_broadcast_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_VIEW") ), 0) AS "job_jobvite_activity.job_jobvite_view_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_CLICK_TO_APPLY") ), 0) AS

"job_jobvite_activity.job_jobvite_click_to_apply_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_FORWARD") ), 0) AS "job_jobvite_activity.job_jobvite_forward_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_APPLY") ), 0) AS "job_jobvite_activity.job_jobvite_apply_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_HIRE") ), 0) AS "job_jobvite_activity.job_jobvite_hire_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_EMAIL_DIRECT") ), 0) AS

"job_jobvite_activity.job_jobvite_email_direct_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_LINKEDIN_DIRECT") ), 0) AS

"job_jobvite_activity.job_jobvite_linkedin_direct_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_LINKEDIN_BROADCAST") ), 0) AS

"job_jobvite_activity.job_jobvite_linkedin_broadcast_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_FACEBOOK_BROADCAST") ), 0) AS

"job_jobvite_activity.job_jobvite_facebook_broadcast_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_TWITTER_BROADCAST") ), 0) AS

"job_jobvite_activity.job_jobvite_twitter_broadcast_sum",

COALESCE(SUM((job_jobvite_activity."JOB_JOBVITE_JOBLINK_BROADCAST") ), 0) AS

"job_jobvite_activity.job_jobvite_joblink_broadcast_sum"

FROM JOBVITE_DWH.JOB AS job

LEFT JOIN JOBVITE_DWH.JOB_JOBVITE_ACTIVITY AS job_jobvite_activity ON (job."JOB_ID") =

(job_jobvite_activity."JOB_ID")

INNER JOIN JOBVITE_DWH.COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID")

WHERE ((((job_jobvite_activity."JOB_JOBVITE_ACTIVITY_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day',

CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND

(job_jobvite_activity."JOB_JOBVITE_ACTIVITY_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29,

DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))))

AND ((job."JOB_ID") = 0 AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0)

GROUP BY 1,2,3,4

ORDER BY 1

User

Query Type

Query

Employee

SELECT

company_user."USER_FIRST_NAME" AS "company_user.user_first_name",

company_user."USER_LAST_NAME" AS "company_user.user_last_name",

company_user."USER_EMAIL" AS "company_user.user_email",

company_user."USER_TITLE" AS "company_user.user_title",

company_user."USER_ADDRESS" AS "company_user.user_address",

company_user."USER_CITY" AS "company_user.user_city",

company_user."USER_POSTALCODE" AS "company_user.user_postalcode",

company_user."USER_STATE" AS "company_user.user_state",

company_user."USER_ROLE" AS "company_user.user_role",

company_user."USER_HOMEPHONE" AS "company_user.user_homephone",

company_user."USER_WORKPHONE" AS "company_user.user_workphone",

company_user."USER_MOBILE" AS "company_user.user_mobile",

company_user."USER_COUNTRY" AS "company_user.user_country",

company_user."USER_ZIP" AS "company_user.user_zip",

company_user."USER_INVITATION_STATUS" AS "company_user.user_invitation_status",

TO_CHAR(CAST(company_user."USER_INVITATION_SENT_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS')

AS "company_user.user_invitation_sent_time",

TO_CHAR(CAST(company_user."USER_INVITATION_ACCEPTED_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD

HH24:MI:SS') AS "company_user.user_invitation_accepted_time",

CASE WHEN company_user."USER_PUBLISHER_STATUS" THEN 'Yes' ELSE 'No' END

AS "company_user.user_publisher_status",

COUNT(*) AS "company_user.count"

FROM COMPANY_USER AS company_user

WHERE (company_user."USER_ACTIVE") AND ((((company_user."USER_INVITATION_SENT_DATE" ) >=

((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))

AS TIMESTAMP_NTZ))) AND (company_user."USER_INVITATION_SENT_DATE" ) < ((CAST(DATEADD('day',

30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS

TIMESTAMP_NTZ))))))

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,CAST(company_user."USER_INVITATION_SENT_DATE" AS

TIMESTAMP_NTZ),CAST(company_user."USER_INVITATION_ACCEPTED_DATE" AS TIMESTAMP_NTZ),18

ORDER BY 1

Hiring Team Metrics

SELECT

company_user_activity_performed_by."USER_FULL_NAME" AS "company_user_activity_performed_by.user_full_name",

company_user_activity_performed_by."USER_ROLE" AS "company_user_activity_performed_by.user_role",

COALESCE(job."JOB_TITLE", 'General Application') AS "job.job_title",

job."JOB_EID" AS "job.job_eid",

TO_CHAR(CAST(company_user_activity_performed_by."USER_LAST_LOGGED_IN" AS TIMESTAMP_NTZ), 'YYYY-MMDD HH24:MI:SS') AS "company_user_activity_performed_by.user_last_logged_in_time",

COALESCE(SUM((user_activity."APPLICATION_COUNT") ), 0) AS "user_activity.sum_application_count",

COALESCE(SUM((user_activity."INTERVIEW_SCHEDULED_COUNT") ), 0) AS

"user_activity.sum_interview_scheduled_count",

COALESCE(SUM((user_activity."MESSAGE_SENT_COUNT") ), 0) AS "user_activity.sum_message_sent_count",

COALESCE(SUM((user_activity."NOTES_COUNT") ), 0) AS "user_activity.sum_notes_count"

FROM JOB AS job

INNER JOIN COMPANY AS company ON (job."COMPANY_ID") = (company."COMPANY_ID")

LEFT JOIN USER_ACTIVITY AS user_activity ON (job."JOB_ID") = (user_activity."JOB_ID")

LEFT JOIN COMPANY_USER AS company_user_activity_performed_by ON (user_activity."USER_ID") =

(company_user_activity_performed_by."USER_ID")

WHERE (((job."JOB_STATUS") = 'Open')) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0

GROUP BY 1,2,3,4,CAST(company_user_activity_performed_by."USER_LAST_LOGGED_IN" AS TIMESTAMP_NTZ)

ORDER BY 1

Onboarding

Query Type	Query
New Hire Census	SELECT onboarding_new_hire."ONB_NEW_HIRE_FULL_NAME" AS "onboarding_new_hire.onb_new_hire_full_name", TO_CHAR(CAST(onboarding_new_hire."ONB_NEW_HIRE_HIRE_DATE" AS TIMESTAMP_NTZ), 'YYYY-MM-DD HH24:MI:SS') AS "onboarding_new_hire.onb_new_hire_hire_time", TO_CHAR(onboarding_new_hire."ONB_NEW_HIRE_START_DATE" , 'YYYY-MM-DD HH24:MI:SS') AS "onboarding_new_hire.onb_new_hire_start_time", company_user_onboarding_manager."USER_FULL_NAME" AS "company_user_onboarding_manager.user_full_name", onboarding_new_hire."ONB_NEW_HIRE_JOB_TITLE" AS "onboarding_new_hire.onb_new_hire_job_title", onboarding_new_hire."ONB_NEW_HIRE_ADDRESS" AS "onboarding_new_hire.onb_new_hire_address", onboarding_new_hire."ONB_NEW_HIRE_HOME_PHONE" AS "onboarding_new_hire.onb_new_hire_home_phone", onboarding_new_hire."ONB_NEW_HIRE_EMAIL" AS "onboarding_new_hire.onb_new_hire_email", onboarding_new_hire."ONB_NEW_HIRE_WORKFLOW_STATUS" AS "onboarding_new_hire.onb_new_hire_workflow_status", COUNT(DISTINCT onboarding_new_hire."ONB_NEW_HIRE_ID" ) AS "onboarding_new_hire.count" FROM ONBOARDING_PROCESS AS onboarding_process LEFT JOIN ONBOARDING_NEW_HIRE AS onboarding_new_hire ON (onboarding_new_hire."ONB_NEW_HIRE_ID") = (onboarding_process."ONB_NEW_HIRE_ID") LEFT JOIN JOB AS job ON (job."JOB_ID") = (onboarding_new_hire."JOB_ID") LEFT JOIN APPLICATION AS application ON (application."APP_ID") = (onboarding_new_hire."APP_ID") LEFT JOIN COMPANY_USER AS company_user_onboarding_manager ON (company_user_onboarding_manager."USER_ID") = (onboarding_new_hire."ONB_NEW_HIRE_HIRING_MANAGER_USER_ID") INNER JOIN COMPANY AS company ON (onboarding_process."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((onboarding_new_hire."ONB_NEW_HIRE_START_DATE" ) >= ((DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))) AND (onboarding_new_hire."ONB_NEW_HIRE_START_DATE" ) < ((DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))))))) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1,CAST(onboarding_new_hire."ONB_NEW_HIRE_HIRE_DATE" AS TIMESTAMP_NTZ),onboarding_new_hire."ONB_NEW_HIRE_START_DATE" ,4,5,6,7,8,9 ORDER BY 1
Onboarding Headcount	SELECT onboarding_process_template."ONB_PROCESS_TEMPLATE_NAME" AS "onboarding_process_template.onb_process_template_name", COUNT(*) AS "onboarding_process.count" FROM ONBOARDING_PROCESS AS onboarding_process LEFT JOIN ONBOARDING_NEW_HIRE AS onboarding_new_hire ON (onboarding_new_hire."ONB_NEW_HIRE_ID") = (onboarding_process."ONB_NEW_HIRE_ID") LEFT JOIN JOB AS job ON (job."JOB_ID") = (onboarding_new_hire."JOB_ID") LEFT JOIN APPLICATION AS application ON (application."APP_ID") = (onboarding_new_hire."APP_ID") LEFT JOIN ONBOARDING_PROCESS_TEMPLATE AS onboarding_process_template ON (onboarding_process."ONB_PROCESS_TEMPLATE_ID") = (onboarding_process_template."ONB_PROCESS_TEMPLATE_ID") INNER JOIN COMPANY AS company ON (onboarding_process."COMPANY_ID") = (company."COMPANY_ID") WHERE (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1 ORDER BY 2 DESC
Onboarding Timeline Report	SELECT onboarding_process_template."ONB_PROCESS_TEMPLATE_NAME" AS "onboarding_process_template.onb_process_template_name", AVG((CASE WHEN DATEDIFF(hour, (TO_CHAR(TO_DATE(CAST(onboarding_new_hire."ONB_NEW_HIRE_CREATED_DATE" AS TIMESTAMP_NTZ)), 'YYYY-MM-DD')), onb_process_actual_kickoff_date) IS NULL THEN 0 ELSE DATEDIFF(hour, (TO_CHAR(TO_DATE(CAST(onboarding_new_hire."ONB_NEW_HIRE_CREATED_DATE" AS TIMESTAMP_NTZ)), 'YYYY-MM-DD')), onb_process_actual_kickoff_date) / 24.0 END) ) AS "op.onb_process_average_waiting_to_be_onboarded_time_in_days", AVG((CASE WHEN DATEDIFF(hour, onb_process_actual_kickoff_date, onb_process_start_date) IS NULL THEN 0 ELSE DATEDIFF(hour, onb_process_actual_kickoff_date, onb_process_start_date) / 24.0 END) ) AS "onboarding_process.onb_process_average_preboarding_time_in_days", AVG((CASE WHEN DATEDIFF(hour, onb_process_start_date, onb_process_completes_on) IS NULL THEN 0 ELSE DATEDIFF(hour, onb_process_start_date, onb_process_completes_on) / 24.0 END) ) AS "onboarding_process.onb_process_average_ramping_time_in_days" FROM ONBOARDING_PROCESS AS onboarding_process LEFT JOIN ONBOARDING_NEW_HIRE AS onboarding_new_hire ON (onboarding_new_hire."ONB_NEW_HIRE_ID") = (onboarding_process."ONB_NEW_HIRE_ID") LEFT JOIN JOB AS job ON (job."JOB_ID") = (onboarding_new_hire."JOB_ID") LEFT JOIN APPLICATION AS application ON (application."APP_ID") = (onboarding_new_hire."APP_ID") LEFT JOIN ONBOARDING_PROCESS_TEMPLATE AS onboarding_process_template ON (onboarding_process."ONB_PROCESS_TEMPLATE_ID") = (onboarding_process_template."ONB_PROCESS_TEMPLATE_ID") INNER JOIN COMPANY AS company ON (onboarding_process."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((onboarding_process."ONB_PROCESS_COMPLETION_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (onboarding_process."ONB_PROCESS_COMPLETION_DATE" ) < (( CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND ((((onboarding_process."ONB_PROCESS_TEMPLATE_ID") IS NOT NULL))) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1 ORDER BY 1
Task Completion Trend	SELECT TO_CHAR(TO_DATE(CAST(DATE_TRUNC('month', onboarding_task."ONB_TASK_COMPLETION_DATE") AS TIMESTAMP_NTZ)), 'YYYY-MM-DD') AS "onboarding_task.onb_task_completion_month_year_sort", TO_CHAR(DATE_TRUNC('month',onboarding_task."ONB_TASK_COMPLETION_DATE"), 'MMMM YYYY') AS "onboarding_task.onb_task_completion_month_year", AVG((DATEDIFF(hour, (TO_CHAR(TO_DATE(CAST(onboarding_task."ONB_TASK_ACTUAL_ACTIVATION_DATE" AS TIMESTAMP_NTZ)), 'YYYY-MM-DD')), (TO_CHAR(TO_DATE(CAST(onboarding_task."ONB_TASK_COMPLETION_DATE" AS TIMESTAMP_NTZ)), 'YYYY-MM-DD')))/24.0) ) AS "onboarding_task.onb_average_task_time_duration_days" FROM ONBOARDING_PROCESS AS onboarding_process LEFT JOIN ONBOARDING_NEW_HIRE AS onboarding_new_hire ON (onboarding_new_hire."ONB_NEW_HIRE_ID") = (onboarding_process."ONB_NEW_HIRE_ID") LEFT JOIN JOB AS job ON (job."JOB_ID") = (onboarding_new_hire."JOB_ID") LEFT JOIN APPLICATION AS application ON (application."APP_ID") = (onboarding_new_hire."APP_ID") LEFT JOIN ONBOARDING_TASK AS onboarding_task ON (onboarding_process."ONB_PROCESS_ID") = (onboarding_task."ONB_TASK_PROCESS_INSTANCE_ID") INNER JOIN JOBVITE_DWH.COMPANY AS company ON (onboarding_process."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((onboarding_task."ONB_TASK_COMPLETION_DATE" ) >= ((CAST(DATEADD('month', -2, DATE_TRUNC('month', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))) AND (onboarding_task."ONB_TASK_COMPLETION_DATE" ) < ((CAST(DATEADD('month', 3, DATEADD('month', -2, DATE_TRUNC('month', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))) AS TIMESTAMP_NTZ)))))) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY TO_DATE(CAST(DATE_TRUNC('month', onboarding_task."ONB_TASK_COMPLETION_DATE") AS TIMESTAMP_NTZ)),2 ORDER BY 1
Task Efficiency Report	SELECT onboarding_task_template."ONB_TASK_TEMPLATE_EXTERNAL_NAME" AS "onboarding_task_template.onb_task_template_external_name", AVG((CASE WHEN DATEDIFF(hour, onb_task_created_date, onb_task_actual_activation_date) IS NULL THEN 0 ELSE DATEDIFF(hour, onb_task_created_date, onb_task_actual_activation_date) / 24.0 END) ) AS "onboarding_task.onb_average_task_scheduled_days", AVG(CASE WHEN (((onboarding_task_template."ONB_TASK_TEMPLATE_ID") IS NOT NULL)) THEN (onboarding_task."ONB_TASK_ASSIGNED_DAYS") ELSE NULL END) AS "onboarding_task.onb_average_task_assigned_days", AVG((onboarding_task."ONB_TASK_BLOCKED_DAYS") ) AS "onboarding_task.onb_average_task_blocked_days", AVG((CASE WHEN DATEDIFF(hour, onb_task_completion_date, onb_task_due_date) IS NULL THEN 0 ELSE DATEDIFF(hour, onb_task_completion_date, onb_task_due_date) / 24.0 END) ) AS "onboarding_task.onb_average_task_completed_days" FROM ONBOARDING_PROCESS AS onboarding_process LEFT JOIN ONBOARDING_NEW_HIRE AS onboarding_new_hire ON (onboarding_new_hire."ONB_NEW_HIRE_ID") = (onboarding_process."ONB_NEW_HIRE_ID") LEFT JOIN ONBOARDING_TASK AS onboarding_task ON (onboarding_process."ONB_PROCESS_ID") = (onboarding_task."ONB_TASK_PROCESS_INSTANCE_ID") LEFT JOIN ONBOARDING_TASK_TEMPLATE AS onboarding_task_template ON (onboarding_task."ONB_TASK_TEMPLATE_ID") = (onboarding_task_template."ONB_TASK_TEMPLATE_ID") LEFT JOIN JOB AS job ON (job."JOB_ID") = (onboarding_new_hire."JOB_ID") LEFT JOIN APPLICATION AS application ON (application."APP_ID") = (onboarding_new_hire."APP_ID") INNER JOIN COMPANY AS company ON (onboarding_process."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((onboarding_task."ONB_TASK_COMPLETION_DATE" ) >= ((CAST(DATEADD('month', -2, DATE_TRUNC('month', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))) AND (onboarding_task."ONB_TASK_COMPLETION_DATE" ) < ((CAST(DATEADD('month', 3, DATEADD('month', -2, DATE_TRUNC('month', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))) AS TIMESTAMP_NTZ)))))) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY 1 ORDER BY 1
Time To Productivity	SELECT TO_CHAR(TO_DATE(CAST(DATE_TRUNC('month', onboarding_process."ONB_PROCESS_COMPLETION_DATE") AS TIMESTAMP_NTZ)), 'YYYY-MM-DD') AS "onboarding_process.onb_process_completion_month_year_sort", TO_CHAR(DATE_TRUNC('month',onboarding_process."ONB_PROCESS_COMPLETION_DATE"), 'MMMM YYYY') AS "onboarding_process.onb_process_completion_month_year", AVG(CASE WHEN (((onboarding_process_template."ONB_PROCESS_TEMPLATE_ID") IS NOT NULL)) THEN (CASE WHEN DATEDIFF(hour, (TO_CHAR(TO_DATE(CAST(onboarding_process."ONB_PROCESS_START_DATE" AS TIMESTAMP_NTZ)), 'YYYY-MM-DD')), (TO_CHAR(TO_DATE(CAST(onboarding_process."ONB_PROCESS_COMPLETION_DATE" AS TIMESTAMP_NTZ)), 'YYYY-MM-DD'))) IS NULL THEN 0 ELSE DATEDIFF(hour, (TO_CHAR(TO_DATE(CAST(onboarding_process."ONB_PROCESS_START_DATE" AS TIMESTAMP_NTZ)), 'YYYY-MM-DD')), (TO_CHAR(TO_DATE(CAST(onboarding_process."ONB_PROCESS_COMPLETES_ON" AS TIMESTAMP_NTZ)), 'YYYY-MM-DD'))) / 24.0 END) ELSE NULL END) AS "onboarding_process.onb_average_process_time_duration_days" FROM ONBOARDING_PROCESS AS onboarding_process LEFT JOIN ONBOARDING_NEW_HIRE AS onboarding_new_hire ON (onboarding_new_hire."ONB_NEW_HIRE_ID") = (onboarding_process."ONB_NEW_HIRE_ID") LEFT JOIN JOB AS job ON (job."JOB_ID") = (onboarding_new_hire."JOB_ID") LEFT JOIN APPLICATION AS application ON (application."APP_ID") = (onboarding_new_hire."APP_ID") LEFT JOIN ONBOARDING_PROCESS_TEMPLATE AS onboarding_process_template ON (onboarding_process."ONB_PROCESS_TEMPLATE_ID") = (onboarding_process_template."ONB_PROCESS_TEMPLATE_ID") INNER JOIN COMPANY AS company ON (onboarding_process."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((onboarding_process."ONB_PROCESS_COMPLETION_DATE" ) >= ((CAST(DATEADD('month', -2, DATE_TRUNC('month', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))) AND (onboarding_process."ONB_PROCESS_COMPLETION_DATE" ) < ((CAST(DATEADD('month', 3, DATEADD('month', -2, DATE_TRUNC('month', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))) AS TIMESTAMP_NTZ)))))) AND (job."JOB_PRIVATE") = false AND (job."JOB_ID") <> 0 GROUP BY TO_DATE(CAST(DATE_TRUNC('month', onboarding_process."ONB_PROCESS_COMPLETION_DATE") AS TIMESTAMP_NTZ)),2 ORDER BY 1 DESC

Summary

Query Type	Query
Bottleneck	WITH candidate_workflow_prev_state_sorting AS ( SELECT CAST(company_id as varchar(20)) \|\| '-' \|\| app_prev_workflow_state_name as pk, company_id, app_prev_workflow_state_name, 0 as app_workflow_workflow_id, MIN(app_prev_workflow_state_position) as minimum_workflow_position, row_number () OVER (partition by company_id order by MIN(app_prev_workflow_state_position), app_prev_workflow_state_name ASC) as sortingkey FROM candidate_workflow GROUP BY 1,2,3 ) SELECT CASE WHEN lower((candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME"))='new' THEN 0 ELSE candidate_workflow_prev_state_sorting.sortingkey END AS "candidate_workflow.app_prev_workflow_state_minimum_position", candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME" AS "candidate_workflow.app_prev_workflow_state_name", cast(SUM((candidate_workflow."APP_WORKFLOW_STATE_IN_DAYS")) as decimal(10,2)) / CAST((COUNT((candidate_workflow."APP_ID"))) as decimal(10,2)) AS "candidate_workflow.avg_workflow_state_in_days" FROM JOBVITE_DWH.APPLICATION AS application LEFT JOIN JOBVITE_DWH.JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN JOBVITE_DWH.COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN JOBVITE_DWH.CANDIDATE_WORKFLOW AS candidate_workflow ON (application."APP_ID") = (candidate_workflow."APP_ID") LEFT JOIN candidate_workflow_prev_state_sorting ON (candidate_workflow."APP_PREV_WORKFLOW_STATE_NAME")=candidate_workflow_prev_state_sorting.app_prev_ workflow_state_name and (candidate_workflow."COMPANY_ID")=candidate_workflow_prev_state_sorting.company_id WHERE (job."JOB_PRIVATE") = false AND (application."JOB_ID") <> 0 GROUP BY 1,2 ORDER BY 3 DESC
Recruiting Funnel	WITH recruiting_funnel_interviewed_derived_sql AS (SELECT a.app_id, CASE WHEN COUNT()>0 or a.app_hire_date IS NOT NULL THEN 1 ELSE 0 END as recruiting_funnel_interviewed FROM application a INNER JOIN candidate_workflow cw on a.app_id=cw.app_id LEFT OUTER JOIN workflow_milestones wm on cw.company_id=wm.company_id AND cw.app_workflow_workflow_id=wm.workflow_id AND wm.workflow_milestones_milestone_type = 'SUBMIT TO HM' WHERE (cw.app_next_workflow_state_kind=1 and (cw.app_next_workflow_state_position > wm.workflow_milestones_milestone_position OR wm.workflow_milestones_milestone_position IS NULL) ) OR (cw.app_next_workflow_state_kind = 0 AND cw.app_next_workflow_state_item_id IN (4,5)) GROUP BY a.app_id, a.app_hire_date ) , recruiting_funnel_offered_derived_sql AS (SELECT a.app_id, CASE WHEN COUNT()>0 or a.app_hire_date IS NOT NULL THEN 1 ELSE 0 END as recruiting_funnel_offered FROM application a INNER JOIN candidate_workflow cw on a.app_id=cw.app_id AND (cw.app_next_workflow_state_item_id=4 OR cw.app_next_workflow_state_item_id=5) AND cw.app_next_workflow_state_kind = 0 GROUP BY a.app_id, a.app_hire_date ) , recruiting_funnel_accepted_derived_sql AS (SELECT a.app_id, CASE WHEN COUNT()>0 or a.app_hire_date IS NOT NULL THEN 1 ELSE 0 END as recruiting_funnel_accepted FROM application a INNER JOIN candidate_workflow cw on a.app_id=cw.app_id AND cw.app_next_workflow_state_item_id=5 AND cw.app_next_workflow_state_kind = 0 GROUP BY a.app_id, a.app_hire_date ) , recruiting_funnel_rejected_derived_sql AS (SELECT app_id, 1 as recruiting_funnel_rejected FROM ( SELECT cw.app_id, FIRST_VALUE(cw.APP_NEXT_WORKFLOW_STATE_ITEM_ID) OVER (PARTITION by cw.APP_ID ORDER BY cw.APP_NEXT_WORKFLOW_STATE_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LATEST_STATE_ITEM_ID, FIRST_VALUE(cw.APP_NEXT_WORKFLOW_STATE_KIND) OVER (PARTITION BY cw.APP_ID ORDER BY cw.APP_NEXT_WORKFLOW_STATE_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LATEST_STATE_KIND, FIRST_VALUE(cw.APP_NEXT_WORKFLOW_STATE_DATE) OVER (PARTITION BY cw.APP_ID ORDER BY cw.APP_NEXT_WORKFLOW_STATE_DATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LATEST_STATE_MOVEMENT_DATE FROM candidate_workflow cw WHERE cw.app_next_workflow_state_item_id<>0 ) T WHERE T.latest_state_item_id = 7 AND t.latest_state_kind = 0 GROUP BY app_id ) SELECT job."JOB_CATEGORY" AS "job.job_category", COUNT() AS "application.count", COALESCE(SUM(recruiting_funnel_rejected_derived_sql.recruiting_funnel_rejected ), 0) AS "rfrds.recruiting_funnel_rejected", CASE WHEN CAST((COUNT(DISTINCT (application."APP_ID") )) as DECIMAL)=0 THEN 0 ELSE CAST((COALESCE(SUM(recruiting_funnel_rejected_derived_sql.recruiting_funnel_rejected ), 0)) as DECIMAL) / CAST((COUNT(DISTINCT (application."APP_ID") )) as DECIMAL) END AS "recruiting_funnel_rejected_derived_sql.percent_rejected", COALESCE(SUM(recruiting_funnel_interviewed_derived_sql.recruiting_funnel_interviewed ), 0) AS "rfids.recruiting_funnel_interviewed", CASE WHEN CAST((COUNT(DISTINCT (application."APP_ID") )) as DECIMAL)=0 THEN 0 ELSE CAST((COALESCE(SUM(recruiting_funnel_interviewed_derived_sql.recruiting_funnel_interviewed ), 0)) as DECIMAL) / CAST((COUNT(DISTINCT (application."APP_ID") )) as DECIMAL) END AS "recruiting_funnel_interviewed_derived_sql.percent_interviewed", COALESCE(SUM(recruiting_funnel_offered_derived_sql.recruiting_funnel_offered ), 0) AS "recruiting_funnel_offered_derived_sql.recruiting_funnel_offered", CASE WHEN CAST((COUNT(DISTINCT (application."APP_ID") )) as DECIMAL)=0 THEN 0 ELSE CAST((COALESCE(SUM(recruiting_funnel_offered_derived_sql.recruiting_funnel_offered ), 0)) as DECIMAL) / CAST((COUNT(DISTINCT (application."APP_ID") )) as DECIMAL) END AS "recruiting_funnel_offered_derived_sql.percent_offered", COALESCE(SUM(recruiting_funnel_accepted_derived_sql.recruiting_funnel_accepted ), 0) AS "rfads.recruiting_funnel_accepted", CASE WHEN CAST((COUNT(DISTINCT (application."APP_ID") )) as DECIMAL)=0 THEN 0 ELSE CAST((COALESCE(SUM(recruiting_funnel_accepted_derived_sql.recruiting_funnel_accepted ), 0)) as DECIMAL) / CAST((COUNT(DISTINCT (application."APP_ID") )) as DECIMAL) END AS "recruiting_funnel_accepted_derived_sql.percent_accepted", COUNT(CASE WHEN (application."APP_HIRE_DATE" IS NOT NULL) THEN 1 ELSE NULL END) AS "application.hired_count", CASE WHEN CAST((COUNT(CASE WHEN (application."APP_HIRE_DATE" IS NOT NULL) THEN 1 ELSE NULL END)) as DECIMAL)=0 THEN 0 ELSE CAST((COUNT(CASE WHEN (application."APP_HIRE_DATE" IS NOT NULL) THEN 1 ELSE NULL END)) as DECIMAL) / CAST((COUNT(*)) as DECIMAL) END AS "application.percent_hired" FROM APPLICATION AS application INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") LEFT JOIN recruiting_funnel_interviewed_derived_sql ON (application."APP_ID") = recruiting_funnel_interviewed_derived_sql.app_id LEFT JOIN recruiting_funnel_offered_derived_sql ON (application."APP_ID") = recruiting_funnel_offered_derived_sql.app_id LEFT JOIN recruiting_funnel_accepted_derived_sql ON (application."APP_ID") = recruiting_funnel_accepted_derived_sql.app_id LEFT JOIN recruiting_funnel_rejected_derived_sql ON (application."APP_ID") = recruiting_funnel_rejected_derived_sql.app_id WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (job."JOB_PRIVATE") = false AND (application."JOB_ID") <> 0 GROUP BY 1 ORDER BY 1
Recruiting Performance	SELECT job."JOB_LOCATION_NAME" AS "job.job_location_name", COUNT(CASE WHEN ((( candidate_milestone_app_sent_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_app_sent_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END) AS "candidate_milestone.count_in_range", COUNT(CASE WHEN ((( candidate_milestone_reject_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_reject_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END) AS "candidate_milestone.reject_count_in_range", COUNT(CASE WHEN ((( candidate_milestone_submit_to_hm_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_submit_to_hm_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END) AS "candidate_milestone.hm_count_in_range", CASE WHEN (COUNT(CASE WHEN ((( candidate_milestone_app_sent_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_app_sent_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END))=0 THEN NULL ELSE CAST((COUNT(CASE WHEN ((( candidate_milestone_submit_to_hm_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_submit_to_hm_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END)) as decimal) / CAST((COUNT(CASE WHEN ((( candidate_milestone_app_sent_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_app_sent_date ) < ((CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/New_York', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))) AS TIMESTAMP_NTZ)))))) THEN 1 ELSE NULL END)) as decimal) END AS "candidate_milestone.sent_to_hm_ratio_in_range", COUNT(CASE WHEN ((( candidate_milestone_first_interview_date ) >= ((CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(DATEADD('day', -29, DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/New_York', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))) AND ( candidate_milestone_first_interview_date ) < ((CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/New_York', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))) AS TIMESTAMP_NTZ)))))) THEN 1 ELSE NULL END) AS "candidate_milestone.interview_count_in_range", COUNT(CASE WHEN ((( candidate_milestone_offer_sent_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_offer_sent_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END) AS "candidate_milestone.offer_count_in_range", COUNT(CASE WHEN ((( candidate_milestone_offer_accept_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_offer_accept_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END) AS "candidate_milestone.accept_count_in_range", CASE WHEN (COUNT(CASE WHEN ((( candidate_milestone_offer_sent_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_offer_sent_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END))=0 THEN NULL ELSE CAST((COUNT(CASE WHEN ((( candidate_milestone_offer_accept_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_offer_accept_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', - 29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END)) as decimal) / cast((COUNT(CASE WHEN ((( candidate_milestone_offer_sent_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_offer_sent_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', - 29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END)) as decimal) END AS "candidate_milestone.accept_ratio_in_range", COUNT(CASE WHEN ((( candidate_milestone_hire_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_hire_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ))))) THEN 1 ELSE NULL END) AS "candidate_milestone.hired_count_in_range" FROM CANDIDATE_MILESTONE AS candidate_milestone INNER JOIN APPLICATION AS application ON (candidate_milestone."APP_ID") = (application."APP_ID") LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (candidate_milestone."COMPANY_ID") = (company."COMPANY_ID") WHERE (CASE WHEN (((( candidate_milestone_offer_accept_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_offer_accept_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) OR (((( candidate_milestone_app_sent_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_app_sent_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) OR (((( candidate_milestone_hire_date ) >= (( CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_hire_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) OR (((( candidate_milestone_submit_to_hm_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_submit_to_hm_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) OR (((( candidate_milestone_first_interview_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_first_interview_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) OR (((( candidate_milestone_offer_sent_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_offer_sent_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) OR (((( candidate_milestone_reject_date ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND ( candidate_milestone_reject_date ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) THEN True Else False End) AND (job."JOB_PRIVATE") = false AND (application."JOB_ID") <> 0 GROUP BY 1 ORDER BY 1
Source Performance	SELECT TO_CHAR(DATE_TRUNC('month',application."APP_SENT_DATE"), 'MMMM YYYY') AS "application.app_sent_month_year", TO_CHAR(TO_DATE(CAST(DATE_TRUNC('month', application."APP_SENT_DATE") AS TIMESTAMP_NTZ)), 'YYYY-MM-DD') AS "application.app_sent_month_year_sort", application."APP_SOURCETYPE" AS "application.app_sourcetype", application."APP_SOURCE" AS "application.app_source", COUNT(*) AS "application.count" FROM APPLICATION AS application LEFT JOIN JOB AS job ON (application."JOB_ID") = (job."JOB_ID") INNER JOIN COMPANY AS company ON (application."COMPANY_ID") = (company."COMPANY_ID") WHERE ((((application."APP_SENT_DATE" ) >= ((CAST(DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS TIMESTAMP_NTZ))) AND (application."APP_SENT_DATE" ) < ((CAST(DATEADD('day', 30, DATEADD('day', -29, DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))) AS TIMESTAMP_NTZ)))))) AND (job."JOB_PRIVATE") = false AND (application."JOB_ID") <> 0 GROUP BY 1,TO_DATE(CAST(DATE_TRUNC('month', application."APP_SENT_DATE") AS TIMESTAMP_NTZ)),3,4
Your Company Hiring Metrics	WITH company_quarterly_benchmark_derived AS ( SELECT CB.COMPANY_QUARTERLY_BENCHMARK_METRIC_DATE, CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_NAME, CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_VALUE, CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_NAME \|\| '_' \|\| CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_VALUE AS COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALUE, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_APPS_PER_REQ, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_APPS_PER_REQ, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_APPS_TO_INTERVIEW_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_APPS_TO_INTERVIEW_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_INTERVIEW_TO_OFFER_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_INTERVIEW_TO_OFFER_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_OFFER_TO_HIRE_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_OFFER_TO_HIRE_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_TIME_TO_HIRE, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_TIME_TO_HIRE, CM.COMPANY_QUARTERLY_METRIC_METRIC_DATE, CM.COMPANY_QUARTERLY_METRIC_TOTAL_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_APPLICANTS, CM.COMPANY_QUARTERLY_METRIC_COMPANY_SIZE, CM.COMPANY_QUARTERLY_METRIC_COMPANY_INDUSTRY, CM.COMPANY_QUARTERLY_METRIC_TOTAL_INTERVIEWS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS_ACCEPTED, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS_DECLINED, CM.COMPANY_QUARTERLY_METRIC_TOTAL_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_APPS_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_INTERVIEWS_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFER_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_SUM_DAYS_TO_FILL, CM.COMPANY_QUARTERLY_METRIC_DATE_TAG FROM COMPANY_QUARTERLY_HIRING_BENCHMARK AS CB LEFT OUTER JOIN COMPANY_QUARTERLY_HIRING_METRIC AS CM ON CB.COMPANY_QUARTERLY_BENCHMARK_METRIC_DATE=CM.COMPANY_QUARTERLY_METRIC_METRIC_DA TE UNION SELECT CB.COMPANY_QUARTERLY_BENCHMARK_METRIC_DATE, CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_NAME, CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_VALUE, 'SIZE_MYSIZE' AS COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALUE, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_APPS_PER_REQ, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_APPS_PER_REQ, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_APPS_TO_INTERVIEW_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_APPS_TO_INTERVIEW_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_INTERVIEW_TO_OFFER_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_INTERVIEW_TO_OFFER_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_OFFER_TO_HIRE_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_OFFER_TO_HIRE_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_TIME_TO_HIRE, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_TIME_TO_HIRE, CM.COMPANY_QUARTERLY_METRIC_METRIC_DATE, CM.COMPANY_QUARTERLY_METRIC_TOTAL_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_APPLICANTS, CM.COMPANY_QUARTERLY_METRIC_COMPANY_SIZE, CM.COMPANY_QUARTERLY_METRIC_COMPANY_INDUSTRY, CM.COMPANY_QUARTERLY_METRIC_TOTAL_INTERVIEWS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS_ACCEPTED, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS_DECLINED, CM.COMPANY_QUARTERLY_METRIC_TOTAL_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_APPS_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_INTERVIEWS_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFER_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_SUM_DAYS_TO_FILL, CM.COMPANY_QUARTERLY_METRIC_DATE_TAG FROM COMPANY_QUARTERLY_HIRING_BENCHMARK AS CB INNER JOIN COMPANY_QUARTERLY_HIRING_METRIC AS CM ON CB.COMPANY_QUARTERLY_BENCHMARK_METRIC_DATE=CM.COMPANY_QUARTERLY_METRIC_METRIC_DA TE AND CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_VALUE = CM.COMPANY_QUARTERLY_METRIC_COMPANY_SIZE UNION SELECT CB.COMPANY_QUARTERLY_BENCHMARK_METRIC_DATE, CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_NAME, CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_VALUE, 'INDUSTRY_MYINDUSTRY' AS COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALUE, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_APPS_PER_REQ, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_APPS_PER_REQ, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_APPS_TO_INTERVIEW_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_APPS_TO_INTERVIEW_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_INTERVIEW_TO_OFFER_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_INTERVIEW_TO_OFFER_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_OFFER_TO_HIRE_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_OFFER_TO_HIRE_RATIO, CB.COMPANY_QUARTERLY_BENCHMARK_AVG_TIME_TO_HIRE, CB.COMPANY_QUARTERLY_BENCHMARK_MEDIAN_TIME_TO_HIRE, CM.COMPANY_QUARTERLY_METRIC_METRIC_DATE, CM.COMPANY_QUARTERLY_METRIC_TOTAL_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_APPLICANTS, CM.COMPANY_QUARTERLY_METRIC_COMPANY_SIZE, CM.COMPANY_QUARTERLY_METRIC_COMPANY_INDUSTRY, CM.COMPANY_QUARTERLY_METRIC_TOTAL_INTERVIEWS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS_ACCEPTED, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS_DECLINED, CM.COMPANY_QUARTERLY_METRIC_TOTAL_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_APPS_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_INTERVIEWS_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_TOTAL_OFFER_TO_HIRED_JOBS, CM.COMPANY_QUARTERLY_METRIC_SUM_DAYS_TO_FILL, CM.COMPANY_QUARTERLY_METRIC_DATE_TAG FROM COMPANY_QUARTERLY_HIRING_BENCHMARK AS CB INNER JOIN COMPANY_QUARTERLY_HIRING_METRIC AS CM ON CB.COMPANY_QUARTERLY_BENCHMARK_METRIC_DATE=CM.COMPANY_QUARTERLY_METRIC_METRIC_DA TE AND coalesce(CB.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_VALUE, 'NOVAL') = coalesce(CM.COMPANY_QUARTERLY_METRIC_COMPANY_INDUSTRY, 'NOVAL') ) SELECT TO_CHAR(DATE_TRUNC('month', CAST(DATE_TRUNC('quarter', company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_METRIC_DATE" ) AS DATE)), 'YYYYMM') AS "company_quarterly_benchmark_derived.metric_date_quarter", COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_APPLICANTS" ), 0) AS "cqbd.company_quarterly_metric_total_applicants", COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_JOBS" ), 0) AS "cqbd.company_quarterly_metric_total_jobs", CASE WHEN (COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_JOBS" ), 0))=0 THEN 0 ELSE CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_APPL ICANTS" ), 0)) as decimal(38,4)) / CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_JOBS " ), 0)) as decimal(38,4)) END AS "company_quarterly_benchmark_derived.apps_per_req", COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_INTERVIEWS" ), 0) AS "cqbd.company_quarterly_metric_total_interviews", CASE WHEN (COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_APPLICANT S" ), 0))=0 THEN 0 ELSE CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_INTE RVIEWS" ), 0)) as decimal(38,4)) / CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_APPL ICANTS" ), 0)) as decimal(38,4)) END AS "company_quarterly_benchmark_derived.apps_to_interview_ratio", COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS" ), 0) AS "cqbd.company_quarterly_metric_total_offers", CASE WHEN (COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_INTERVIEW S" ), 0))=0 THEN 0 ELSE CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_OFFE RS" ), 0)) as decimal(38,4)) / CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_INTE RVIEWS" ), 0)) as decimal(38,4)) END AS "company_quarterly_benchmark_derived.interview_to_offer_ratio", COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS_ACCEPTED" ), 0) AS "cqbd.company_quarterly_metric_total_offers_accepted", CASE WHEN (COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_OFFERS" ), 0))=0 THEN 0 ELSE CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_OFFE RS_ACCEPTED" ), 0)) as decimal(38,4)) / CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_OFFE RS" ), 0)) as decimal(38,4)) END AS "company_quarterly_benchmark_derived.offer_to_hire_ratio", CASE WHEN (COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_HIRED_JOB S" ), 0))=0 THEN 0 ELSE CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_SUM_DAYS_ TO_FILL" ), 0)) as decimal(38,4)) / CAST((COALESCE(SUM(company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_TOTAL_HIRE D_JOBS" ), 0)) as decimal(38,4)) END AS "company_quarterly_benchmark_derived.time_to_hire" FROM company_quarterly_benchmark_derived WHERE (((CASE WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_MYSIZE' THEN 'Size: Similar to my company' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_MYINDUSTRY' THEN 'Industry: Same as my company' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_0-250' THEN '0-250' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_251-500' THEN '251-500' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_501-2500' THEN '501-2500' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_2501-5000' THEN '2501-5000' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_5000+' THEN '5000+' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Agriculture' THEN 'Industry: Agriculture' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Construction' THEN 'Industry: Construction' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Education' THEN 'Industry: Education' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Financial Services' THEN 'Industry: Financial Services' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Government' THEN 'Industry: Government' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Healthcare' THEN 'Industry: Healthcare' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Holding Companies' THEN 'Industry: Holding Companies' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Hospitality' THEN 'Industry: Hospitality' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Information Technology' THEN 'Industry: Information Technology' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Manufacturing' THEN 'Industry: Manufacturing' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Media and Telecommunications' THEN 'Industry: Media and Telecommunications' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Mining Oil and Gas' THEN 'Industry: Mining Oil and Gas' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Non-profit' THEN 'Industry: Non-Profit' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Oil & Energy' THEN 'Industry: Oil' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Professional Services' THEN 'Industry: Professional Services' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Real Estate' THEN 'Industry: Real Estate' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Retail' THEN 'Industry: Retail' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Transportation and Logistics' THEN 'Industry: Transportation and Logistics' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Utilities' THEN 'Industry: Utilities' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Wholesale' THEN 'Industry: Wholesale' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_K: Other' THEN 'Industry: Other' ELSE 'Industry: Other' END) = 'Size: Similar to my company')) AND ((((company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_METRIC_DATE" ) >= ((DATEADD('month', -12, CAST(DATE_TRUNC('quarter', CAST(DATE_TRUNC('quarter', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS DATE)) AS DATE)))) AND (company_quarterly_benchmark_derived."COMPANY_QUARTERLY_METRIC_METRIC_DATE" ) < ((DATEADD('month', 12, CAST(DATE_TRUNC('quarter', DATEADD('month', -12, CAST(DATE_TRUNC('quarter', CAST(DATE_TRUNC('quarter', DATE_TRUNC('day', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))) AS DATE)) AS DATE))) AS DATE))))))) AND ((CASE WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_MYSIZE' THEN 'Size: Similar to my company' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_MYINDUSTRY' THEN 'Industry: Same as my company' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_0-250' THEN '0-250' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_251-500' THEN '251-500' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_501-2500' THEN '501-2500' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_2501-5000' THEN '2501-5000' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='SIZE_5000+' THEN '5000+' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Agriculture' THEN 'Industry: Agriculture' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Construction' THEN 'Industry: Construction' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Education' THEN 'Industry: Education' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Financial Services' THEN 'Industry: Financial Services' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Government' THEN 'Industry: Government' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Healthcare' THEN 'Industry: Healthcare' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Holding Companies' THEN 'Industry: Holding Companies' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Hospitality' THEN 'Industry: Hospitality' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Information Technology' THEN 'Industry: Information Technology' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Manufacturing' THEN 'Industry: Manufacturing' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Media and Telecommunications' THEN 'Industry: Media and Telecommunications' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Mining Oil and Gas' THEN 'Industry: Mining Oil and Gas' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Non-profit' THEN 'Industry: Non-Profit' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Oil & Energy' THEN 'Industry: Oil' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Professional Services' THEN 'Industry: Professional Services' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Real Estate' THEN 'Industry: Real Estate' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Retail' THEN 'Industry: Retail' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Transportation and Logistics' THEN 'Industry: Transportation and Logistics' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Utilities' THEN 'Industry: Utilities' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_Wholesale' THEN 'Industry: Wholesale' WHEN company_quarterly_benchmark_derived.COMPANY_QUARTERLY_BENCHMARK_BENCHMARK_DIMENSION_VALU E='INDUSTRY_K: Other' THEN 'Industry: Other' ELSE 'Industry: Other' END)<>'Filter Out') GROUP BY 1 ORDER BY 1 DESC

Additional Resources

Data Exchange

Data Exchange - Connection Instructions and FAQ's

Related to