draft_of_greenhouse_activities_v1.sql

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