Last active
August 12, 2021 16:33
-
-
Save gareginordyan/157654eb6ca9e55f332b8f138c36d214 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with interview_events as ( | |
select | |
scheduled_interview.id as sch_interview_id, | |
scheduled_interview.application_id, | |
date(scheduled_interview.start) as activity_date, | |
interview.name as interview_name, | |
case | |
when lower(job_stage.name) = 'initial screen' then 'Initial Screen' | |
when lower(job_stage.name) = 'second screen' then 'Second Screen' | |
when lower(job_stage.name) like '%screen%' then 'Other Screen' | |
when lower(job_stage.name) like '%onsite%' then 'Onsite Interview' | |
when lower(job_stage.name) = 'final interview' then 'Final Interview' | |
else 'Other Interview' | |
end as activity_group | |
from greenhouse.scheduled_interview | |
join greenhouse.interview on scheduled_interview.interview_id = interview.id | |
join greenhouse.application on scheduled_interview.application_id = application.id | |
join greenhouse.job_application on application.id = job_application.application_id --TODO might intros a few dups application - job is many to 1 | |
join greenhouse.job_stage on interview.job_stage_id = job_stage.id | |
where not scheduled_interview._fivetran_deleted | |
), aggregate_to_interview_activity_group as ( | |
select | |
application_id, | |
activity_group, | |
min(activity_date) as activity_group_date | |
from interview_events | |
group by 1, 2 | |
), offer_activity_groups as ( | |
select | |
application_id, | |
case | |
when status = 'Created' then 'Offer Created' | |
when status = 'Accepted' then 'Offer Accepted' | |
when status = 'Rejected' then 'Offer Rejected' | |
else 'Offer Other' | |
end as activity_group, | |
min(date(updated_at)) as activity_group_date | |
from greenhouse.offer_history | |
group by 1, 2 | |
order by 1, 3 | |
) | |
select | |
application_id, | |
activity_group_date, | |
activity_group | |
from aggregate_to_interview_activity_group | |
union all | |
select | |
application_id, | |
activity_group_date, | |
activity_group | |
from offer_activity_groups | |
order by 1, 2 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment