select case when action_type != 'New Candidate' then '"'||name||'"' end as from_stage, case when action_type = 'New Candidate' then '"'||name||'"' when action != 'Candidate disqualified'then right(action, len(action) - 25) when action = 'Candidate disqualified' then 'Disqualified' end as to_stage , action, action_type,stage_kind, job_id, candidate_id from activities left join stages on stage_id = stages.id where (action_type = 'New Candidate' or action = 'Candidate disqualified' or stage_kind is not null) id 20 [id] [candidates] id 1 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" candidate_created_at 7 [candidate_created_at] [candidates] candidate_created_at 2 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" job_id 3 [job_id] [candidates] job_id 3 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" job_title 130 [job_title] [candidates] job_title 4 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" job_department 130 [job_department] [candidates] job_department 5 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" job_first_published_at 7 [job_first_published_at] [candidates] job_first_published_at 6 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" name 130 [name] [candidates] name 7 string Count 400 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" firstname 130 [firstname] [candidates] firstname 8 string Count 400 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" lastname 130 [lastname] [candidates] lastname 9 string Count 400 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" headline 130 [headline] [candidates] headline 10 string Count 400 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" email 130 [email] [candidates] email 11 string Count 400 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" application_method 130 [application_method] [candidates] application_method 12 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" source_category 130 [source_category] [candidates] source_category 13 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" source_domain 130 [source_domain] [candidates] source_domain 14 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" source 130 [source] [candidates] source 15 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" outlet 130 [outlet] [candidates] outlet 16 string Count 100 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" current_stage_id 3 [current_stage_id] [candidates] current_stage_id 17 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" current_stage_name 130 [current_stage_name] [candidates] current_stage_name 18 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" source_member_id 3 [source_member_id] [candidates] source_member_id 19 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" last_unanswered_email_at 7 [last_unanswered_email_at] [candidates] last_unanswered_email_at 20 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_screened_at 7 [first_screened_at] [candidates] first_screened_at 21 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_contacted_at 7 [first_contacted_at] [candidates] first_contacted_at 22 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_interviewed_at 7 [first_interviewed_at] [candidates] first_interviewed_at 23 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_offer_at 7 [first_offer_at] [candidates] first_offer_at 24 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_hired_at 7 [first_hired_at] [candidates] first_hired_at 25 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" disqualified 11 [disqualified] [candidates] disqualified 26 boolean Count true "SQL_BIT" "SQL_C_BIT" disqualified_at 7 [disqualified_at] [candidates] disqualified_at 27 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" disqualification_reason 130 [disqualification_reason] [candidates] disqualification_reason 28 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" id 20 [id (activities)] [activities] id 30 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" activity_created_at 7 [activity_created_at] [activities] activity_created_at 31 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" action 130 [action] [activities] action 32 string Count 1156 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" action_type 130 [action_type] [activities] action_type 33 string Count 765 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" stage_kind 130 [stage_kind] [activities] stage_kind 34 string Count 100 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" stage_id 20 [stage_id] [activities] stage_id 35 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" job_id 20 [job_id (activities)] [activities] job_id 36 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" job_title 130 [job_title (activities)] [activities] job_title 37 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" job_department 130 [job_department (activities)] [activities] job_department 38 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" candidate_id 20 [candidate_id] [activities] candidate_id 39 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" candidate_name 130 [candidate_name] [activities] candidate_name 40 string Count 400 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" member_id 20 [member_id] [activities] member_id 41 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" member_name 130 [member_name] [activities] member_name 42 string Count 765 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" member_is_recruiter 11 [member_is_recruiter] [activities] member_is_recruiter 43 boolean Count true "SQL_BIT" "SQL_C_BIT" id 20 [id (jobs)] [jobs] id 45 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" job_created_at 7 [job_created_at] [jobs] job_created_at 46 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" title 130 [title] [jobs] title 47 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" department 130 [department] [jobs] department 48 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" code 130 [code] [jobs] code 49 string Count 1000 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" state 130 [state] [jobs] state 50 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" shortcode 130 [shortcode] [jobs] shortcode 51 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" country_code 130 [country_code] [jobs] country_code 52 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" state_code 130 [state_code] [jobs] state_code 53 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" city 130 [city] [jobs] city 54 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" zip_code 130 [zip_code] [jobs] zip_code 55 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" telecommuting 11 [telecommuting] [jobs] telecommuting 56 boolean Count true "SQL_BIT" "SQL_C_BIT" advertise_elsewhere 11 [advertise_elsewhere] [jobs] advertise_elsewhere 57 boolean Count true "SQL_BIT" "SQL_C_BIT" advertise_country_code 130 [advertise_country_code] [jobs] advertise_country_code 58 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" advertise_state_code 130 [advertise_state_code] [jobs] advertise_state_code 59 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" advertise_city 130 [advertise_city] [jobs] advertise_city 60 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" advertise_zip_code 130 [advertise_zip_code] [jobs] advertise_zip_code 61 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" employment_type 130 [employment_type] [jobs] employment_type 62 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" function 130 [function] [jobs] function 63 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" industry 130 [industry] [jobs] industry 64 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" experience 130 [experience] [jobs] experience 65 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" education 130 [education] [jobs] education 66 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" salary_currency 130 [salary_currency] [jobs] salary_currency 67 string Count 255 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" salary_from 3 [salary_from] [jobs] salary_from 68 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" salary_to 3 [salary_to] [jobs] salary_to 69 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" first_published_at 7 [first_published_at] [jobs] first_published_at 70 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" last_published_at 7 [last_published_at] [jobs] last_published_at 71 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_screening_at 7 [first_screening_at] [jobs] first_screening_at 72 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_interview_at 7 [first_interview_at] [jobs] first_interview_at 73 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_offer_at 7 [first_offer_at (jobs)] [jobs] first_offer_at 74 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" first_hire_at 7 [first_hire_at] [jobs] first_hire_at 75 datetime Year true "SQL_TYPE_TIMESTAMP" "SQL_C_TYPE_TIMESTAMP" advertise_cost 131 [advertise_cost] [jobs] advertise_cost 76 real Sum 8 4 true "SQL_NUMERIC" "SQL_C_NUMERIC" candidates_count 3 [candidates_count] [jobs] candidates_count 77 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" free_postings_count 3 [free_postings_count] [jobs] free_postings_count 78 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" paid_postings_count 3 [paid_postings_count] [jobs] paid_postings_count 79 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" owned_postings_count 3 [owned_postings_count] [jobs] owned_postings_count 80 integer Sum 10 true "SQL_INTEGER" "SQL_C_LONG" from_stage 130 [from_stage] [custom sql query] from_stage 1 string Count 767 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" to_stage 130 [to_stage] [custom sql query] to_stage 2 string Count 1156 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" action 130 [action (custom sql query)] [custom sql query] action 3 string Count 1156 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" action_type 130 [action_type (custom sql query)] [custom sql query] action_type 4 string Count 765 true true "SQL_WLONGVARCHAR" "SQL_C_WCHAR" stage_kind 130 [stage_kind (custom sql query)] [custom sql query] stage_kind 5 string Count 100 true "SQL_WVARCHAR" "SQL_C_WCHAR" "true" job_id 20 [job_id (custom sql query)] [custom sql query] job_id 6 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" candidate_id 20 [candidate_id (custom sql query)] [custom sql query] candidate_id 7 integer Sum 19 true "SQL_BIGINT" "SQL_C_SBIGINT" [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[none:to_stage:nk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[none:from_stage:nk][redshift.147iy5w0196yfd11xrvsk1gtbttm].[none:to_stage:nk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[tmn:candidate_created_at:qk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Activity Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:id:qk][redshift.147iy5w0196yfd11xrvsk1gtbttm].[tmn:candidate_created_at:qk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[none:action_type:nk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:candidate_id:qk][redshift.147iy5w0196yfd11xrvsk1gtbttm].[tmn:activity_created_at:qk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Longitude (generated)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Latitude (generated)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Activity Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Latitude (generated)][redshift.147iy5w0196yfd11xrvsk1gtbttm].[Longitude (generated)] "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_242912909104529409:qk]" "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_123989731431211014:qk]" "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_123989731433545735:qk]" "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_242912909103812608:qk]" "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_172896008276234241:qk]" [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Multiple Values] "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_242912909104529409:qk]" 1813.0 [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Multiple Values] "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_123989731431211014:qk]" 1436.0 [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Multiple Values] "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_123989731433545735:qk]" 305.0 [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Multiple Values] "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_242912909103812608:qk]" 100.0 [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[:Measure Names] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Multiple Values] "[redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:Calculation_172896008276234241:qk]" 85.0 [redshift.147iy5w0196yfd11xrvsk1gtbttm].[tmn:job_created_at:qk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Activity Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)] ([redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:id (jobs):qk] + [redshift.147iy5w0196yfd11xrvsk1gtbttm].[usr:Calculation_123989731422150659:qk])[redshift.147iy5w0196yfd11xrvsk1gtbttm].[tmn:job_created_at:qk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Activity Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[tmn:job_created_at:qk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Activity Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[ctd:id (jobs):qk][redshift.147iy5w0196yfd11xrvsk1gtbttm].[tmn:job_created_at:qk] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Activity Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Activity Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Department)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (Country Code)] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Job Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Candidate Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (MONTH(Activity Created At))] [redshift.147iy5w0196yfd11xrvsk1gtbttm].[Action (from_stage,to_stage)]