select
    profile_id,
    application_id,
    application_source_type,
    application_source_name,
    application_ts,
    application_hired_ts,
    ats_job_id,
    position_id,
    application_match_score,
    candidate_id,
    application_inferred_gender,
    application_inferred_race,
    application_is_referral,
    application_is_internal,
    application_is_career_site,
    application_in_talent_network,
    application_status,
    system_id,
    utm_source,
    utm_campaign,
    application_last_stage,
    application_last_stage_group,
    application_last_stage_ts,
    application_rejection_reason,
    stage_group,
    stage,
    stage_ts,
    stage_group_index,
    ef_stage_group,
    stage_group_display_name,
    position_title,
    position_location,
    position_location_country,
    position_status,
    position_inferred_seniority_level,
    position_job_function,
    position_business_unit,
    position_employment_type,
    position_calibration_status,
    position_type,
    position_recruiter,
    position_hiring_manager,
    position_recruiter_name,
    position_hiring_manager_name,
    position_supervisory_org,
    position_is_open,
    position_is_posted,
    position_creation_ts,
    position_is_confidential,
    number_of_openings,
    position_earliest_opened_at,
    position_pay_rate_type,
    candidate_self_declared_gender,
    candidate_self_declared_race,
    candidate_first_name,
    candidate_last_name,
    profile_email,
    profile_title,
    new_applicant_ts,
    phonescreen_ts,
    offer_ts,
    onsite_interview_ts,
    hired_advanced_ts,
    position_close_ts,
    manager_email_of_recruiter,
    time_to_fill_days
from
    public._ta_leaders_hiring_outcomes

select
    unique_id,
    embedding_id,
    event,
    user_email,
    vs_cookie,
    request_url,
    interface,
    country,
    t_create,
    rating,
    max_rating,
    utm_source,
    utm_campaign,
    question_template_id,
    question_id,
    question_label,
    answer,
    is_promoter,
    is_comment,
    position_id
from public._survey_analytics
where embedding_id = 'pcs_apply_thank_you_page'

-- Server Log with Positions Dataset Query
-- Extracted from the QuickSight dataset definition

SELECT 
  -- Server log columns
  logs.unique_id,
  logs.event,
  logs.user_email,
  logs.namespace,
  logs.t_create,
  logs.latency_milliseconds,
  logs.interface,
  logs.response_code,
  logs.request_path,
  logs.referrer_path,
  logs.request_method,
  logs.vs_cookie,
  logs.query,
  logs.referrer,
  logs.redirect_url,
  logs.user_company,
  logs.request_endpoint,
  logs.user_agent,
  logs.hostname,
  logs.position_id,
  logs.country,
  logs.utm_source,
  logs.utm_campaign,
  logs.vs_cookie_position_id,
  logs.ai_recommended,
  logs.from_job_alerts,
  logs.pcs_num_events,
  logs.is_ai_recommended,
  
  -- Position table columns (renamed position_id as position_id[positions])
  pos.position_id AS "position_id[positions]",
  pos.title,
  pos.location,
  pos.location_country,
  pos.status,
  pos.is_open,
  pos.is_posted,
  pos.creation_ts,
  pos.last_modified_ts,
  pos.seniority_level,
  pos.job_function,
  pos.location_latitude,
  pos.location_longitude,
  pos.business_unit,
  pos.ats_job_id,
  pos.system_id,
  pos.employment_type,
  pos.position_seniority,
  pos.pay_rate_type,
  pos.is_confidential,
  pos.number_of_openings,
  pos.calibration_status,
  pos.title_cluster,
  pos.position_type,
  pos.calibration_template_id,
  pos.workflow_template_id,
  pos.recruiter,
  pos.recruiter_name,
  pos.hiring_manager,
  pos.hiring_manager_name,
  pos.supervisory_org,
  pos.has_calibrated_location,
  pos.has_calibrated_skills,
  pos.has_calibrated_titles,
  pos.has_calibrated_similar_cand,
  pos.has_calibrated_workexp,
  
  -- Calculated columns based on the data transformations
  CASE 
    WHEN logs.event = 'api_get_job' THEN 'Visit'
    WHEN logs.event = 'click_apply_to_job' THEN 'Application Start'
    WHEN logs.event = 'api_post_resume' THEN 'Resume Upload'
    WHEN logs.event = 'apply_to_job' THEN 'Application Submit'
    ELSE logs.event
  END AS Event,
  
  CASE 
    WHEN logs.interface = 'mobile' THEN 'Mobile'
    WHEN logs.interface = 'tablet' THEN 'Tablet'
    ELSE 'Desktop'
  END AS Interface

FROM server_log_analytics logs
LEFT JOIN positions pos 
  ON logs.position_id = pos.position_id;

select
    profile_id,
    application_id,
    application_source_type,
    application_source_name,
    application_ts,
    application_hired_ts,
    ats_job_id,
    position_id,
    application_match_score,
    candidate_id,
    application_inferred_gender,
    application_inferred_race,
    application_is_referral,
    application_is_internal,
    application_is_career_site,
    application_in_talent_network,
    application_status,
    system_id,
    utm_source,
    utm_campaign,
    application_last_stage,
    application_last_stage_group,
    application_last_stage_ts,
    application_rejection_reason,
    stage_group,
    stage,
    stage_ts,
    position_title,
    position_location,
    position_location_country,
    position_status,
    position_inferred_seniority_level,
    position_job_function,
    position_business_unit,
    position_employment_type,
    position_calibration_status,
    position_type,
    position_recruiter,
    position_hiring_manager,
    position_recruiter_name,
    position_hiring_manager_name,
    position_supervisory_org,
    position_is_open,
    position_is_posted,
    position_creation_ts,
    position_is_confidential,
    number_of_openings,
    position_earliest_opened_at,
    position_pay_rate_type
from public._ta_leaders_hiring_outcomes

-- Server Log with Positions Dataset Query
-- Extracted from the QuickSight dataset definition

SELECT 
  -- Server log columns
  logs.unique_id,
  logs.event,
  logs.user_email,
  logs.namespace,
  logs.t_create,
  logs.latency_milliseconds,
  logs.interface,
  logs.response_code,
  logs.request_path,
  logs.referrer_path,
  logs.request_method,
  logs.vs_cookie,
  logs.query,
  logs.referrer,
  logs.redirect_url,
  logs.user_company,
  logs.request_endpoint,
  logs.user_agent,
  logs.hostname,
  logs.position_id,
  logs.country,
  logs.utm_source,
  logs.utm_campaign,
  logs.vs_cookie_position_id,
  logs.ai_recommended,
  logs.from_job_alerts,
  logs.pcs_num_events,
  logs.is_ai_recommended,
  
  -- Position table columns (renamed position_id as position_id[positions])
  pos.position_id AS "position_id[positions]",
  pos.title,
  pos.location,
  pos.location_country,
  pos.status,
  pos.is_open,
  pos.is_posted,
  pos.creation_ts,
  pos.last_modified_ts,
  pos.seniority_level,
  pos.job_function,
  pos.location_latitude,
  pos.location_longitude,
  pos.business_unit,
  pos.ats_job_id,
  pos.system_id,
  pos.employment_type,
  pos.position_seniority,
  pos.pay_rate_type,
  pos.is_confidential,
  pos.number_of_openings,
  pos.calibration_status,
  pos.title_cluster,
  pos.position_type,
  pos.calibration_template_id,
  pos.workflow_template_id,
  pos.recruiter,
  pos.recruiter_name,
  pos.hiring_manager,
  pos.hiring_manager_name,
  pos.supervisory_org,
  pos.has_calibrated_location,
  pos.has_calibrated_skills,
  pos.has_calibrated_titles,
  pos.has_calibrated_similar_cand,
  pos.has_calibrated_workexp,
  
  -- Calculated columns based on the data transformations
  CASE 
    WHEN logs.event = 'api_get_job' THEN 'Visit'
    WHEN logs.event = 'click_apply_to_job' THEN 'Application Start'
    WHEN logs.event = 'api_post_resume' THEN 'Resume Upload'
    WHEN logs.event = 'apply_to_job' THEN 'Application Submit'
    ELSE logs.event
  END AS Event,
  
  CASE 
    WHEN logs.interface = 'mobile' THEN 'Mobile'
    WHEN logs.interface = 'tablet' THEN 'Tablet'
    ELSE 'Desktop'
  END AS Interface

FROM server_log_analytics logs
LEFT JOIN positions pos 
  ON logs.position_id = pos.position_id;

select
    profile_id,
    application_id,
    application_source_type,
    application_source_name,
    application_ts,
    application_hired_ts,
    ats_job_id,
    position_id,
    application_match_score,
    candidate_id,
    application_inferred_gender,
    application_inferred_race,
    application_is_referral,
    application_is_internal,
    application_is_career_site,
    application_in_talent_network,
    application_status,
    system_id,
    utm_source,
    utm_campaign,
    application_last_stage,
    application_last_stage_group,
    application_last_stage_ts,
    application_rejection_reason,
    stage_group,
    stage,
    stage_ts,
    stage_group_index,
    ef_stage_group,
    stage_group_display_name,
    position_title,
    position_location,
    position_location_country,
    position_status,
    position_inferred_seniority_level,
    position_job_function,
    position_business_unit,
    position_employment_type,
    position_calibration_status,
    position_type,
    position_recruiter,
    position_hiring_manager,
    position_recruiter_name,
    position_hiring_manager_name,
    position_supervisory_org,
    position_is_open,
    position_is_posted,
    position_creation_ts,
    position_is_confidential,
    number_of_openings,
    position_earliest_opened_at,
    position_pay_rate_type,
    candidate_self_declared_gender,
    candidate_self_declared_race,
    candidate_first_name,
    candidate_last_name,
    profile_email,
    profile_title,
    new_applicant_ts,
    phonescreen_ts,
    offer_ts,
    onsite_interview_ts,
    hired_advanced_ts,
    position_close_ts,
    manager_email_of_recruiter,
    time_to_fill_days
from
    public._ta_leaders_hiring_outcomes

select
    action_user,
    application_id,
    application_last_stage,
    application_last_stage_group,
    application_last_stage_ts,
    application_status,
    application_ts,
    ats_job_id,
    business_unit,
    calibration_status,
    candidate_id,
    hired_ts,
    hiring_manager,
    hiring_manager_name,
    in_talent_network,
    is_career_site,
    is_internal,
    is_referral,
    job_function,
    last_touch_source_name,
    last_touch_source_type,
    match_score,
    multi_touch_source,
    multi_touch_source_name,
    multi_touch_source_type,
    multi_touch_sourcing_ts,
    number_of_openings,
    position_creation_ts,
    position_earliest_opened_at,
    position_id,
    position_is_open,
    position_is_posted,
    position_location,
    position_location_country,
    position_seniority_level,
    position_status,
    position_title,
    postion_is_confidential,
    profile_id,
    recruiter,
    recruiter_name,
    system_id,
    utm_campaign,
    utm_source
from public._application_source_multitouch

SELECT
    profile_id,
    application_id,
    application_ts,
    hired_ts,
    application_last_stage_group,
    application_last_stage,
    application_last_stage_ts,
    ats_job_id,
    position_id,
    match_score,
    application_status,
    position_title,
    position_location,
    position_location_country,
    position_status,
    position_is_open,
    position_is_posted,
    position_creation_ts,
    position_seniority_level,
    job_function,
    business_unit,
    LISTAGG(DISTINCT multi_touch_source_category, ' + ') within group (order by multi_touch_sourcing_ts) as source_path
FROM (
    SELECT
        profile_id,
        application_id,
        application_ts,
        hired_ts,
        application_last_stage_group,
        application_last_stage,
        application_last_stage_ts,
        ats_job_id,
        position_id,
        match_score,
        application_status,
        position_title,
        position_location,
        position_location_country,
        position_status,
        position_is_open,
        position_is_posted,
        position_creation_ts,
        position_seniority_level,
        job_function,
        business_unit,
        multi_touch_sourcing_ts,
        case
            when lower(multi_touch_source_name) like '%extension%' then 'Recruiter (sourcing via EF Chrome extensions)'
            when multi_touch_source_type = 'Talent Network' then 'Eightfold Talent Network'
            when multi_touch_source_type = 'Contacted' then 'Recruiter (sourcing via email/SMS/Whatsapp)'
            when multi_touch_source_type = 'Internal' then 'Internal Mobility'
            when multi_touch_source_type = 'Imported' then 'Recruiter (importing applications)'
            when multi_touch_source_type = 'Campaign' then 'Eightfold Campaign'
            when multi_touch_source_type = 'Extension' then 'Recruiter (sourcing via EF Chrome extensions)'
            when multi_touch_source_type = 'EFN' then 'Recruiter (requesting leads from Talent Network)'
            when multi_touch_source_type = 'Recruiting Event' then 'Recruiting Event'
            when lower(multi_touch_source_type) = 'rif portal' then 'RIF Portal'
            else multi_touch_source_type
        end as multi_touch_source_category
    FROM public._application_source_multitouch
    WHERE system_id != 'sourcing'
)
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21

select
    profile_id,
    application_id,
    application_source_type,
    application_source_name,
    application_ts,
    application_hired_ts,
    ats_job_id,
    position_id,
    application_match_score,
    candidate_id,
    application_inferred_gender,
    application_inferred_race,
    application_is_referral,
    application_is_internal,
    application_is_career_site,
    application_in_talent_network,
    application_status,
    system_id,
    utm_source,
    utm_campaign,
    application_last_stage,
    application_last_stage_group,
    application_last_stage_ts,
    application_rejection_reason,
    stage_group,
    stage,
    stage_ts,
    stage_group_index,
    ef_stage_group,
    stage_group_display_name,
    position_title,
    position_location,
    position_location_country,
    position_status,
    position_inferred_seniority_level,
    position_job_function,
    position_business_unit,
    position_employment_type,
    position_calibration_status,
    position_type,
    position_recruiter,
    position_hiring_manager,
    position_recruiter_name,
    position_hiring_manager_name,
    position_supervisory_org,
    position_is_open,
    position_is_posted,
    position_creation_ts,
    position_is_confidential,
    number_of_openings,
    position_earliest_opened_at,
    position_pay_rate_type,
    candidate_self_declared_gender,
    candidate_self_declared_race,
    candidate_first_name,
    candidate_last_name,
    profile_email,
    profile_title,
    new_applicant_ts,
    phonescreen_ts,
    offer_ts,
    onsite_interview_ts,
    hired_advanced_ts,
    position_close_ts,
    manager_email_of_recruiter,
    time_to_fill_days
from
    public._ta_leaders_hiring_outcomes

SELECT
    p.position_id,
    p.creation_ts as position_creation_ts,
    p.closed_at as position_closed_at,
    p.is_open as position_is_open,
    p.is_posted as position_is_posted,
    (
        case when trim(p.title) = ''
        OR trim(p.title) = 'Unknown' then 'Title Not Assigned'
        when p.title IS NULL then 'Others'
        else p.title
        end
    ) as position_title,
    (
        case when trim(location) = ''
        OR trim(location) = 'Unknown' then 'Location Not Assigned'
        when location IS NULL then 'Others'
        else location
        end
    ) as position_location,
    [... extensive data cleaning logic with multiple CASE statements ...]
FROM
    public._positions p
    LEFT JOIN [... application and stage aggregations ...]
WHERE
    p.system_id != 'sourcing'

SELECT
    profile_id,
    position_id,
    application_id,
    application_ts,
    position_recruiter_name,
    position_hiring_manager_name,
    position_title,
    position_job_function,
    position_business_unit,
    ats_job_id,
    position_location,
    position_location_country,
    application_status,
    position_status,
    position_is_open,
    position_is_posted,
    position_is_confidential,
    position_creation_ts,
    new_applicant_ts,
    phonescreen_ts,
    onsite_interview_ts,
    offer_ts,
    hired_ts,
    position_close_ts,
    candidate_name
FROM public._recruitment_funnel_time_to_interview

SELECT
    feedback_id,
    feedback_status,
    requested_timestamp,
    responded_timestamp,
    position_id,
    profile_id,
    feedback_type,
    feedback_template_id,
    user_calendar_event_id,
    position_creation_ts,
    position_closed_at,
    position_archived_at,
    position_is_open,
    position_is_posted,
    position_title,
    position_location,
    position_recruiter_name,
    position_hiring_manager_name,
    position_location_country,
    position_status,
    position_job_function,
    position_business_unit,
    requester_email,
    reviewer_email,
    title,
    hiring_manager_name,
    recruiter_name,
    job_function,
    business_unit,
    LOCATION,
    location_country,
    requester_name,
    reviewer_name
FROM public._recruitment_funnel_feedback_requests

SELECT
    position_id,
    conversation_type,
    position_creation_ts,
    position_closed_at,
    position_archived_at,
    position_is_open,
    position_is_posted,
    campaign_indicator,
    position_title,
    position_location,
    position_recruiter_name,
    position_hiring_manager_name,
    position_location_country,
    position_status,
    position_job_function,
    position_business_unit,
    conversations_sent,
    conversations_read,
    conversations_replied,
    profiles_contacted,
    profiles_read,
    profiles_replied,
    applications_received,
    total_applicants,
    hired_applications,
    hired_applicants
FROM public._recruitment_funnel_outreach_metrics

SELECT 
    unique_id,
    embedding_id,
    event,
    user_email,
    vs_cookie,
    request_url,
    interface,
    country,
    t_create,
    rating,
    max_rating,
    utm_source,
    utm_campaign,
    question_template_id,
    question_id,
    question_label,
    answer,
    is_promoter,
    is_comment,
    position_id,
    vs_cookie_position_id,
    is_pcs_survey
FROM survey_analytics;

-- Server Log with Positions Dataset Query
-- Extracted from the QuickSight dataset definition

SELECT 
  -- Server log columns
  logs.unique_id,
  logs.event,
  logs.user_email,
  logs.namespace,
  logs.t_create,
  logs.latency_milliseconds,
  logs.interface,
  logs.response_code,
  logs.request_path,
  logs.referrer_path,
  logs.request_method,
  logs.vs_cookie,
  logs.query,
  logs.referrer,
  logs.redirect_url,
  logs.user_company,
  logs.request_endpoint,
  logs.user_agent,
  logs.hostname,
  logs.position_id,
  logs.country,
  logs.utm_source,
  logs.utm_campaign,
  logs.vs_cookie_position_id,
  logs.ai_recommended,
  logs.from_job_alerts,
  logs.pcs_num_events,
  logs.is_ai_recommended,
  
  -- Position table columns (renamed position_id as position_id[positions])
  pos.position_id AS "position_id[positions]",
  pos.title,
  pos.location,
  pos.location_country,
  pos.status,
  pos.is_open,
  pos.is_posted,
  pos.creation_ts,
  pos.last_modified_ts,
  pos.seniority_level,
  pos.job_function,
  pos.location_latitude,
  pos.location_longitude,
  pos.business_unit,
  pos.ats_job_id,
  pos.system_id,
  pos.employment_type,
  pos.position_seniority,
  pos.pay_rate_type,
  pos.is_confidential,
  pos.number_of_openings,
  pos.calibration_status,
  pos.title_cluster,
  pos.position_type,
  pos.calibration_template_id,
  pos.workflow_template_id,
  pos.recruiter,
  pos.recruiter_name,
  pos.hiring_manager,
  pos.hiring_manager_name,
  pos.supervisory_org,
  pos.has_calibrated_location,
  pos.has_calibrated_skills,
  pos.has_calibrated_titles,
  pos.has_calibrated_similar_cand,
  pos.has_calibrated_workexp,
  
  -- Calculated columns based on the data transformations
  CASE 
    WHEN logs.event = 'api_get_job' THEN 'Visit'
    WHEN logs.event = 'click_apply_to_job' THEN 'Application Start'
    WHEN logs.event = 'api_post_resume' THEN 'Resume Upload'
    WHEN logs.event = 'apply_to_job' THEN 'Application Submit'
    ELSE logs.event
  END AS Event,
  
  CASE 
    WHEN logs.interface = 'mobile' THEN 'Mobile'
    WHEN logs.interface = 'tablet' THEN 'Tablet'
    ELSE 'Desktop'
  END AS Interface

FROM server_log_analytics logs
LEFT JOIN positions pos 
  ON logs.position_id = pos.position_id;

-- TA Outcomes Dataset Query
-- This query replicates the join logic from the QuickSight dataset definition
-- Stage table columns are renamed with [stage] suffix
-- Position table columns are renamed with [positions] suffix  
-- Profile table columns are renamed with [profile] suffix

SELECT 
    -- Stage table columns (with renamed aliases)
    s.stage_group,
    s.stage,
    s.profile_id AS "profile_id[stage]",
    s.system_id AS "system_id[stage]", 
    s.stage_ts,
    s.application_id AS "application_id[stage]",
    s.position_id AS "position_id[stage]",
    
    -- Application table columns (original names)
    a.profile_id,
    a.application_id,
    a.source_type,
    a.source_name,
    a.application_ts,
    a.hired_ts,
    a.last_stage,
    a.last_stage_ts,
    a.ef_last_stage,
    a.ef_last_stage_ts,
    a.ats_job_id,
    a.position_id,
    a.match_stars,
    a.candidate_id,
    a.is_referral,
    a.is_internal,
    a.status,
    a.rejection_reason,
    a.system_id,
    a.in_talent_network,
    a.last_stage_group,
    a.utm_source,
    a.utm_campaign,
    a.ef_gender,
    a.ef_race,
    a.ef_veteran_status,
    
    -- Position table columns (with renamed aliases)
    p.position_id AS "position_id[positions]",
    p.title,
    p.location,
    p.location_country,
    p.status AS "status[positions]",
    p.is_open,
    p.is_posted,
    p.creation_ts,
    p.last_modified_ts,
    p.seniority_level,
    p.job_function,
    p.location_latitude,
    p.location_longitude,
    p.business_unit,
    p.ats_job_id AS "ats_job_id[positions]",
    p.system_id AS "system_id[positions]",
    p.employment_type,
    p.position_seniority,
    p.pay_rate_type,
    p.is_confidential,
    p.number_of_openings,
    p.calibration_status,
    p.title_cluster,
    p.position_type,
    p.calibration_template_id,
    p.workflow_template_id,
    p.recruiter,
    p.recruiter_name,
    p.hiring_manager,
    p.hiring_manager_name,
    p.supervisory_org,
    p.has_calibrated_location,
    p.has_calibrated_skills,
    p.has_calibrated_titles,
    p.has_calibrated_similar_cand,
    p.has_calibrated_workexp,
    
    -- Profile table columns (with renamed aliases)
    pr.profile_id AS "profile_id[profile]",
    pr.system_id AS "system_id[profile]",
    pr.first_name,
    pr.last_name,
    pr.enc_profile_id,
    pr.gender,
    pr.ef_gender AS "ef_gender[profile]",
    pr.title AS "title[profile]",
    pr.last_modified_ts AS "last_modified_ts[profile]",
    pr.candidate_id AS "candidate_id[profile]",
    pr.location_country AS "location_country[profile]",
    pr.location_state,
    pr.location_city,
    pr.location_county,
    pr.current_company,
    pr.exp_years,
    pr.highest_degree,
    pr.latest_major,
    pr.has_resume,
    pr.is_thin_profile,
    pr.num_skills,
    pr.ef_race AS "ef_race[profile]",
    pr.seniority_level AS "seniority_level[profile]"

FROM stage_table s

-- Join 1: Stage + Application (LEFT JOIN)
LEFT JOIN application_table a
    ON s.application_id = a.application_id 
    AND s.profile_id = a.profile_id
    AND s.position_id = a.position_id 
    AND s.system_id = a.system_id

-- Join 2: Previous result + Position (LEFT JOIN)  
LEFT JOIN position_table p
    ON s.position_id = p.position_id 
    AND s.system_id = p.system_id

-- Join 3: Previous result + Profile (LEFT JOIN)
LEFT JOIN profile_table pr
    ON s.profile_id = pr.profile_id 
    AND s.system_id = pr.system_id;

select
    datepart('year',activity_timestamp) as year,
    datepart('week',activity_timestamp) as week,
    DATE_TRUNC('week' , activity_timestamp) as start_of_week,
    activity_type,
    active_user_id,
    profile_id,
    position_id,
    activity_timestamp,
    active_user_name,
    manager_name,
    employee_title,
    employee_level,
    employee_seniority,
    employee_line_of_business,
    employee_business_unit,
    is_alumni,
    employee_location,
    employee_location_country,
    case when is_recruiter then 'Yes' else 'No' end as recruiter_role_indicator,
    case when is_hm then 'Yes' else 'No' end as hiring_manager_role_indicator,
    case when is_sourcer then 'Yes' else 'No' end as sourcer_role_indicator,
    case when is_event_team_external then 'Yes' else 'No' end as event_team_memeber_indicator,
    race,
    gender,
    ef_gender,
    ef_race,
    sum(feature_usage) as feature_usage
from public._ta_source_activity_log
group by 1, 2,3,4,5,6,7,8, 9, 10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26

select
    year,
    month,
    start_of_month,
    activity_type,
    active_users_this_month,
    feature_usage_count,
    frequency_of_usage_per_user,
    repeat_users_following_month,
    repeat_users_percent
from
    (
        select
            year,
            month,
            start_of_month,
            activity_type,
            count(distinct active_user_id) as active_users_this_month,
            sum(feature_usage) as feature_usage_count,
            sum(feature_usage)/ count(distinct active_user_id) as frequency_of_usage_per_user,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    month, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            ) as repeat_users_following_month,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    month, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            )* 1.00 / count(distinct active_user_id) as repeat_users_percent
        FROM
            (
                select
                    datepart('year', activity_timestamp) as year,
                    datepart('month', activity_timestamp) as month,
                    DATE_TRUNC('month', activity_timestamp) as start_of_month,
                    activity_type,
                    active_user_id,
                    activity_timestamp,
                    feature_usage,
                    LAG(activity_timestamp) OVER (
                        PARTITION BY active_user_id, activity_type
                        ORDER BY
                            activity_timestamp
                    ) AS previous_activity_timestamp_ts
                from
                    public._ta_source_activity_log
                where active_user_id not like 'import%'
            )
        group by
            1,
            2,
            3,
            4
    )

select
    year,
    week,
    start_of_week,
    activity_type,
    active_users_this_week,
    feature_usage_count,
    frequency_of_usage_per_user,
    repeat_users_following_week,
    repeat_users_percent
from
    (
        select
            year,
            week,
            start_of_week,
            activity_type,
            count(distinct active_user_id) as active_users_this_week,
            sum(feature_usage) as feature_usage_count,
            sum(feature_usage)/ count(distinct active_user_id) as frequency_of_usage_per_user,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    week, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            ) as repeat_users_following_week,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    week, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            )* 1.00 / count(distinct active_user_id) as repeat_users_percent
        FROM
            (
                select
                    datepart('year', activity_timestamp) as year,
                    datepart('week', activity_timestamp) as week,
                    DATE_TRUNC('week', activity_timestamp) as start_of_week,
                    activity_type,
                    active_user_id,
                    activity_timestamp,
                    feature_usage,
                    LAG(activity_timestamp) OVER (
                        PARTITION BY active_user_id,  activity_type
                        ORDER BY
                            activity_timestamp
                    ) AS previous_activity_timestamp_ts
                from
                    public._ta_source_activity_log
                where active_user_id not like 'import%'
            ) t
        group by
            1,
            2,
            3,
            4
    ) t1

SELECT
    count(distinct t1.profile_id) as total_employees,
    count(
        distinct case when (t2.activity_timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_sourcing_employees,
    count(
        distinct case when (
            is_recruiter
            OR is_event_team_external
            OR is_sourcer
        ) then t1.profile_id end
    ) as total_potential_sourcing_employees,
    count(
        distinct case when (
            is_recruiter
            OR is_event_team_external
            OR is_sourcer
        )
        AND (t2.activity_timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_employees_among_potential_sourcers,
    count(
        distinct case when is_recruiter then t1.profile_id end
    ) as total_recruiters,
    count(
        distinct case when is_recruiter
        AND (t2.activity_timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_recuiters,
    count(
        distinct case when is_sourcer then t1.profile_id end
    ) as total_sourcers,
    count(
        distinct case when is_sourcer
        AND (t2.activity_timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_sourcers,
    [... detailed breakdowns by sourcing tool type ...]
    count(
        distinct case when t2.active_user_id IS NOT NULL then t1.profile_id end
    ) * 100.00 / count(distinct t1.profile_id) as percent_employees_using_sourcing_features,
    count(
        distinct case when is_sourcer
        AND t2.active_user_id IS NOT NULL then t1.profile_id end
    ) * 100.00 / count(
        distinct case when is_sourcer then t1.profile_id end
    ) as percent_sourcers_using_sourcing_features,
    count(
        distinct case when (
            is_recruiter
            OR is_event_team_external
            OR is_sourcer
        )
        AND t2.active_user_id IS NOT NULL then t1.profile_id end
    ) * 100.00 / count(
        distinct case when (
            is_recruiter
            OR is_event_team_external
            OR is_sourcer
        ) then t1.profile_id end
    ) as percent_potential_sourcers_using_sourcing_features
FROM
    (
        select
            employee_email,
            profile_id,
            hiring_date,
            is_sourcer,
            is_recruiter,
            is_event_team_external
        from
            public._employee
        WHERE
            is_alumni = FALSE
            and employee_id is not null and employee_id != ''
    ) t1
    LEFT JOIN (
        SELECT
            activity_type,
            activity_timestamp,
            active_user_id
        from
            public._ta_source_activity_log
    ) t2 on trim(
        lower(t1.employee_email)
    ) = trim(
        lower(t2.active_user_id)
    )

SELECT
    year,
    week,
    start_of_week,
    activity_type,
    active_user_id,
    active_user_name,
    manager_name,
    feature_usage,
    concat(
        candidate_first_name,
        concat(' ', candidate_last_name)
    ) as applicant_name,
    t0.profile_id as candidate_sourced_profile_id,
    t1.profile_id as applicant_profile_id,
    min_phonescreened_date,
    min_interviewed_date,
    min_hired_date,
    diverse_candidate_indicator,
    application_ts,
    activity_timestamp,
    t1.position_id,
    position_title,
    position_creation_ts,
    position_job_function,
    position_business_unit,
    employee_title,
    employee_level,
    employee_seniority,
    employee_line_of_business,
    employee_business_unit,
    is_alumni,
    employee_location,
    employee_location_country,
    recruiter_role_indicator,
    hiring_manager_role_indicator,
    sourcer_role_indicator,
    event_team_memeber_indicator,
    race,
    gender,
    ef_gender,
    ef_race,
    candidate_gender,
    candidate_ef_gender,
    candidate_race,
    candidate_ef_race
from
    (
        select
            datepart('year', activity_timestamp) as year,
            datepart('week', activity_timestamp) as week,
            DATE_TRUNC('week', activity_timestamp) as start_of_week,
            activity_type,
            active_user_id,
            profile_id,
            position_id,
            activity_timestamp,
            active_user_name,
            manager_name,
            employee_title,
            employee_level,
            employee_seniority,
            employee_line_of_business,
            employee_business_unit,
            is_alumni,
            employee_location,
            employee_location_country,
            case when is_recruiter then 'Yes' else 'No' end as recruiter_role_indicator,
            case when is_hm then 'Yes' else 'No' end as hiring_manager_role_indicator,
            case when is_sourcer then 'Yes' else 'No' end as sourcer_role_indicator,
            case when is_event_team_external then 'Yes' else 'No' end as event_team_memeber_indicator,
            race,
            gender,
            ef_gender,
            ef_race,
            sum(feature_usage) as feature_usage
        from
            public._ta_source_activity_log
        group by
            [... 26 columns for grouping ...]
    ) t0
    left join [... hiring outcomes analysis ...]
    left join [... candidate profile details ...]

select
    year,
    month,
    start_of_month,
    activity_type,
    active_users_this_month,
    messages_sent_count,
    frequency_of_usage_per_user,
    repeat_users_following_month,
    repeat_users_percent
from
    (
        select
            t1.year,
            t1.month,
            t1.start_of_month,
            t1.activity_type,
            count(distinct t1.active_user_id) as active_users_this_month,
            count(distinct id) as messages_sent_count,
            count(distinct id) / count(distinct t1.active_user_id) as frequency_of_usage_per_user,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    month, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            ) as repeat_users_following_month,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    month, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            )* 1.00 / count(distinct active_user_id) as repeat_users_percent
        FROM
            (
                SELECT
                    datepart('year', created_at) as year,
                    datepart('month', created_at) as month,
                    DATE_TRUNC('month', created_at) as start_of_month,
                    case when user_campaign_id IS NOT NULL then 'Campaign' 
                         when user_campaign_id IS NULL and conversation_type = 'email' then 'Email' 
                         when user_campaign_id IS NULL and conversation_type = 'sms' then 'SMS' 
                         when user_campaign_id IS NULL and conversation_type = 'whatsapp' then 'Whatsapp' 
                         else conversation_type end as activity_type,
                    user_id as active_user_id,
                    id,
                    created_at as activity_timestamp,
                    LAG(created_at) OVER (
                        PARTITION BY active_user_id, activity_type
                        ORDER BY
                            created_at
                    ) AS previous_activity_timestamp_ts
                FROM
                    public._user_messages
                WHERE
                    conversation_id IN (
                        'contact', 'campaign', 'sms_send',
                        'whatsapp_send'
                    )
                    and user_id not like 'import%'
            ) t1
        where
            t1.activity_type != 'others'
        group by
            1,
            2,
            3,
            4
    ) t1

select
    year,
    week,
    start_of_week,
    activity_type,
    active_users_this_week,
    messages_sent_count,
    frequency_of_usage_per_user,
    repeat_users_following_week,
    repeat_users_percent
from
    (
        select
            t1.year,
            t1.week,
            t1.start_of_week,
            t1.activity_type,
            count(distinct t1.active_user_id) as active_users_this_week,
            count(distinct id) as messages_sent_count,
            count(distinct id) / count(distinct t1.active_user_id) as frequency_of_usage_per_user,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    week, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            ) as repeat_users_following_week,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    week, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            )* 1.00 / count(distinct active_user_id) as repeat_users_percent
        FROM
            (
                SELECT
                    datepart('year', created_at) as year,
                    datepart('week', created_at) as week,
                    DATE_TRUNC('week', created_at) as start_of_week,
                    case when user_campaign_id IS NOT NULL then 'Campaign' 
                         when user_campaign_id IS NULL and trim(conversation_type) = 'email' then 'Email' 
                         when user_campaign_id IS NULL and trim(conversation_type) = 'sms' then 'SMS' 
                         when user_campaign_id IS NULL and trim(conversation_type) = 'whatsapp' then 'Whatsapp' 
                         else conversation_type end as activity_type,
                    user_id as active_user_id,
                    id,
                    created_at as activity_timestamp,
                    LAG(created_at) OVER (
                        PARTITION BY active_user_id, activity_type
                        ORDER BY
                            created_at
                    ) AS previous_activity_timestamp_ts
                FROM
                    public._user_messages
                WHERE
                    conversation_id IN (
                        'contact', 'campaign', 'sms_send',
                        'whatsapp_send'
                    )
                    and user_id not like 'import%'
            ) t1
        where
            t1.activity_type != 'others'
        group by
            1,
            2,
            3,
            4
    ) t1

SELECT
    count(distinct t1.profile_id) as total_employees,
    count(
        distinct case when (t2.activity_timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_employees,
    count(
        distinct case when is_recruiter then t1.profile_id end
    ) as total_recruiters,
    count(
        distinct case when is_recruiter
        AND (t2.activity_timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_recuiters,
    count(
        distinct case when is_hm then t1.profile_id end
    ) as total_hiring_manager,
    count(
        distinct case when is_hm
        AND (t2.activity_timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_hiring_managers,
    count(
        distinct case when t2.active_user_id IS NOT NULL
        and t2.activity_type = 'Campaign' then t1.profile_id end
    ) as active_employees_using_campaign,
    count(
        distinct case when t2.active_user_id IS NOT NULL
        and t2.activity_type IN ('Email', 'SMS', 'Whatsapp') then t1.profile_id end
    ) as active_employees_using_email_sms_whatsapp,
    count(
        distinct case when t2.active_user_id IS NOT NULL
        and t2.activity_type = 'Email' then t1.profile_id end
    ) as active_employees_using_email,
    [... detailed breakdowns by engagement type and employee role ...]
    count(
        distinct case when t2.active_user_id IS NOT NULL then t1.profile_id end
    ) * 100.00 / count(distinct t1.profile_id) as percent_employees_using_engagement_features
FROM
    (
        select
            employee_email,
            profile_id,
            hiring_date,
            is_hm,
            is_recruiter
        from
            public._employee
        WHERE
            is_alumni = FALSE
            and employee_id is not null and employee_id != ''
    ) t1
    LEFT JOIN (
        SELECT
            user_id as active_user_id,
            case
                when user_campaign_id IS NOT NULL then 'Campaign'
                when user_campaign_id IS NULL and trim(conversation_type) = 'email' then 'Email'
                when user_campaign_id IS NULL and trim(conversation_type) = 'sms' then 'SMS'
                when user_campaign_id IS NULL and trim(conversation_type) = 'whatsapp' then 'Whatsapp'
                else conversation_type
            end as activity_type,
            created_at as activity_timestamp
        FROM
            public._user_messages
        WHERE
            conversation_id IN (
                'contact', 'campaign', 'sms_send',
                'whatsapp_send'
            )
        group by
            1,
            2,
            3
    ) t2 on trim(
        lower(t1.employee_email)
    ) = trim(
        lower(t2.active_user_id)
    )

SELECT
    activity_type,
    conversation_type,
    trim(lower(active_user_id)) as active_user_id,
    concat(
        t3.first_name,
        concat(' ', t3.last_name)
    ) as active_user_name,
    manager_name,
    t1.position_id,
    t1.user_campaign_id,
    t4.campaign_name,
    t1.profile_id,
    id,
    created_at,
    reply_time,
    min_application_ts,
    diverse_candidate_indicator,
    application_match_score,
    min_phonescreened_date,
    min_interviewed_date,
    min_hired_date,
    last_updated_stage,
    position_business_unit,
    position_job_function,
    position_location,
    position_location_country,
    position_recruiter_name,
    position_hiring_manager_name,
    employee_title,
    employee_level,
    employee_seniority,
    employee_line_of_business,
    employee_business_unit,
    is_alumni,
    employee_location,
    employee_location_country,
    recruiter_role_flag,
    hiring_manager_role_flag,
    sourcer_role_flag,
    event_team_member_flag,
    race,
    gender,
    ef_gender,
    ef_race
FROM
    (
        SELECT
            group_id,
            case when user_campaign_id IS NOT NULL then 'Campaign' 
                 when user_campaign_id IS NULL and trim(conversation_type) = 'email' then 'Email' 
                 when user_campaign_id IS NULL and trim(conversation_type) = 'sms' then 'SMS' 
                 when user_campaign_id IS NULL and trim(conversation_type) = 'whatsapp' then 'Whatsapp' 
                 else conversation_type end as activity_type,
            conversation_type,
            user_id as active_user_id,
            position_id,
            user_campaign_id,
            profile_id,
            id,
            created_at,
            reply_time
        FROM
            public._user_messages
        WHERE
            conversation_id IN (
                'contact', 'campaign', 'sms_send',
                'whatsapp_send'
            )
    ) t1
    left join [... hiring outcomes analysis ...]
    left join [... employee details ...]
    left join [... campaign details ...]

select
    'Campaign' as activity_type,
    campaign_sourcer_user_id,
    concat(
        t3.first_name,
        concat(' ', t3.last_name)
    ) as active_user_name,
    manager_name,
    t1.application_id,
    t1.position_id,
    t1.profile_id,
    campaign_name,
    campaign_sourcing_ts,
    min_application_ts,
    diverse_candidate_indicator,
    application_match_score,
    min_phonescreened_date,
    min_interviewed_date,
    min_hired_date,
    last_updated_stage,
    position_business_unit,
    position_job_function,
    position_location,
    position_location_country,
    position_recruiter_name,
    position_hiring_manager_name,
    employee_title,
    employee_level,
    employee_seniority,
    employee_line_of_business,
    employee_business_unit,
    is_alumni,
    employee_location,
    employee_location_country,
    recruiter_role_flag,
    hiring_manager_role_flag,
    sourcer_role_flag,
    event_team_member_flag,
    race,
    gender,
    ef_gender,
    ef_race
from
    (
        select
            t1.application_id,
            t1.profile_id,
            t1.position_id,
            t1.source as campaign_name,
            t1.sourcing_ts as campaign_sourcing_ts,
            t1.source_name as campaign_sourcer_user_id,
            t2.position_business_unit,
            t2.position_job_function,
            t2.position_location,
            t2.position_location_country,
            t2.position_recruiter_name,
            t2.position_hiring_manager_name,
            t2.diverse_candidate_indicator,
            t2.min_phonescreened_date,
            t2.min_interviewed_date,
            t2.min_offer_date,
            t2.min_hired_date,
            t2.last_updated_stage,
            t2.min_application_ts,
            t2.application_match_score
        from public._application_source t1
        inner join
            (
                select
                    application_id,
                    profile_id,
                    position_id,
                    position_business_unit,
                    position_job_function,
                    position_location,
                    position_location_country,
                    position_recruiter_name,
                    position_hiring_manager_name,
                    (case when application_inferred_gender = 'female' then 1 else 0 end) as diverse_candidate_indicator,
                    min(phonescreen_ts) as min_phonescreened_date,
                    min(onsite_interview_ts) as min_interviewed_date,
                    min(offer_ts) as min_offer_date,
                    min(application_hired_ts) as min_hired_date,
                    max(application_last_stage_ts) as last_updated_stage,
                    min(application_ts) as min_application_ts,
                    avg(application_match_score) as application_match_score
                from public._ta_leaders_hiring_outcomes
                group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
            ) t2
        on t1.application_id = t2.application_id
        and t1.profile_id = t2.profile_id
        where t1.source_type = 'Campaign'
    ) t1
    left join
    (
        select
            employee_email,
            manager_email,
            first_name,
            last_name,
            manager_name,
            case when title IS NULL then 'Title not assigned' else title end as employee_title,
            case when level IS NULL then 'Level not assigned' else level end as employee_level,
            case when seniority IS NULL then 'Seniority not assigned' else seniority end as employee_seniority,
            case when lob IS NULL then 'Level of Business not assigned' else lob end as employee_line_of_business,
            case when business_unit IS NULL then 'Business unit not assigned' else business_unit end as employee_business_unit,
            is_alumni,
            case when location IS NULL then 'Location not found' else location end as employee_location,
            case when location_country IS NULL then 'Location Country not found' else location end as employee_location_country,
            case when is_recruiter = 1 then 'Yes' else 'No' end as recruiter_role_flag,
            case when is_hm = 1 then 'Yes' else 'No' end as hiring_manager_role_flag,
            case when is_sourcer= 1 then 'Yes' else 'No' end as sourcer_role_flag,
            case when is_event_team_external = 1 then 'Yes' else 'No' end as event_team_member_flag,
            race,
            gender,
            ef_gender,
            ef_race
        from public._employee
    ) t3
    on trim(lower(t1.campaign_sourcer_user_id)) = trim(lower(t3.employee_email))

select
    year,
    month,
    start_of_month,
    feedback_type,
    active_users_this_month,
    feature_usage,
    frequency_of_usage_per_user,
    repeat_users_following_month,
    repeat_users_percent
from
    (
        select
            year,
            month,
            start_of_month,
            feedback_type,
            count(distinct active_user_id) as active_users_this_month,
            count(distinct id) as feature_usage,
            count(distinct id) / count(distinct active_user_id) as frequency_of_usage_per_user,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    month, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            ) as repeat_users_following_month,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    month, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            )* 1.00 / count(distinct active_user_id) as repeat_users_percent
        FROM
            (
                SELECT
                    datepart('year', created_at) as year,
                    datepart('month', created_at) as month,
                    DATE_TRUNC('month', created_at) as start_of_month,
                    case when feedback_type = 'quick_feedback' then 'Quick Feedback' when feedback_type = 'interview_feedback' then 'Interview Feedback' else feedback_type end as feedback_type,
                    trim(lower(requester_email)) as active_user_id,
                    id,
                    created_at as activity_timestamp,
                    LAG(created_at) OVER (
                        PARTITION BY active_user_id, feedback_type
                        ORDER BY
                            created_at
                    ) AS previous_activity_timestamp_ts
                FROM
                    public._profile_feedback
                WHERE archive_flag = FALSE
                    and requester_email not like 'import%'
            )
        group by
            1,
            2,
            3,
            4
    ) t1

select
    year,
    week,
    start_of_week,
    feedback_type,
    active_users_this_week,
    feature_usage,
    frequency_of_usage_per_user,
    repeat_users_following_week,
    repeat_users_percent
from
    (
        select
            year,
            week,
            start_of_week,
            feedback_type,
            count(distinct active_user_id) as active_users_this_week,
            count(distinct id) as feature_usage,
            count(distinct id) / count(distinct active_user_id) as frequency_of_usage_per_user,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    week, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            ) as repeat_users_following_week,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    week, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            )* 1.00 / count(distinct active_user_id) as repeat_users_percent
        FROM
            (
                SELECT
                    datepart('year', created_at) as year,
                    datepart('week', created_at) as week,
                    DATE_TRUNC('week', created_at) as start_of_week,
                    case when feedback_type = 'quick_feedback' then 'Quick Feedback' when feedback_type = 'interview_feedback' then 'Interview Feedback' else feedback_type end as feedback_type,
                    trim(lower(requester_email)) as active_user_id,
                    id,
                    created_at as activity_timestamp,
                    LAG(created_at) OVER (
                        PARTITION BY active_user_id, feedback_type
                        ORDER BY
                            created_at
                    ) AS previous_activity_timestamp_ts
                FROM
                    public._profile_feedback
                WHERE archive_flag = FALSE
                    and requester_email not like 'import%'
            )
        group by
            1,
            2,
            3,
            4
    ) t1

SELECT
    count(distinct t1.profile_id) as total_employees,
    count(
        distinct case when requester_email IS NOT NULL then t1.profile_id end
    ) as total_active_employees_requesting_feedback,
    count(
        distinct case when is_recruiter then t1.profile_id end
    ) as total_recruiters,
    count(
        distinct case when is_recruiter
        AND (t2.created_at IS NOT NULL) then t1.profile_id end
    ) as total_active_recruiters_requesting_feeback,
    count(
        distinct case when feedback_type = 'Quick Feedback'
        and (t2.created_at IS NOT NULL) then t1.profile_id end
    ) as total_active_employees_requesting_quick_feeback,
    count(
        distinct case when feedback_type = 'Interview Feedback'
        and (t2.created_at IS NOT NULL) then t1.profile_id end
    ) as total_active_employees_requesting_interview_feeback,
    count(
        distinct case when is_recruiter
        AND feedback_type = 'Quick Feedback'
        and (t2.created_at IS NOT NULL) then t1.profile_id end
    ) as total_active_recruiters_requesting_quick_feeback,
    count(
        distinct case when is_recruiter
        AND feedback_type = 'Interview Feedback'
        and (t2.created_at IS NOT NULL) then t1.profile_id end
    ) as total_active_recruiters_requesting_interview_feeback
FROM
    (
        select
            employee_email,
            profile_id,
            hiring_date,
            is_recruiter
        from
            public._employee
        WHERE
            is_alumni = FALSE
            and employee_id is not null and employee_id != ''
    ) t1
    LEFT JOIN (
        SELECT
            case when feedback_type = 'quick_feedback' then 'Quick Feedback' when feedback_type = 'interview_feedback' then 'Interview Feedback' else feedback_type end as feedback_type,
            requester_email,
            created_at
        FROM
            public._profile_feedback
        WHERE archive_flag = FALSE
    ) t2 on trim(
        lower(t1.employee_email)
    ) = trim(
        lower(t2.requester_email)
    )

SELECT
    count(distinct t1.position_id) as total_positions,
    count(
        distinct case when (t2.created_at IS NOT NULL) then t1.position_id end
    ) as total_positions_using_feeback,
    count(
        distinct case when feedback_type = 'Quick Feedback'
        and (t2.created_at IS NOT NULL) then t1.position_id end
    ) as total_positions_using_quick_feeback,
    count(
        distinct case when feedback_type = 'Interview Feedback'
        and (t2.created_at IS NOT NULL) then t1.position_id end
    ) as total_positions_using_interview_feeback,
    count(
        distinct case when is_open = FALSE then t1.position_id end
    ) as total_closed_positions,
    count(
        distinct case when (t2.created_at IS NOT NULL)
        AND is_open = FALSE then t1.position_id end
    ) as total_closed_positions_using_feeback,
    count(
        distinct case when feedback_type = 'Quick Feedback'
        and (t2.created_at IS NOT NULL)
        AND is_open = FALSE then t1.position_id end
    ) as total_closed_positions_using_quick_feeback,
    count(
        distinct case when feedback_type = 'Interview Feedback'
        and (t2.created_at IS NOT NULL)
        AND is_open = FALSE then t1.position_id end
    ) as total_closed_positions_using_interview_feeback,
    count(
        distinct case when is_open = TRUE then t1.position_id end
    ) as total_open_positions,
    count(
        distinct case when (t2.created_at IS NOT NULL)
        AND is_open = TRUE then t1.position_id end
    ) as total_open_positions_using_feeback,
    count(
        distinct case when feedback_type = 'Quick Feedback'
        and (t2.created_at IS NOT NULL)
        AND is_open = TRUE then t1.position_id end
    ) as total_open_positions_using_quick_feeback,
    count(
        distinct case when feedback_type = 'Interview Feedback'
        and (t2.created_at IS NOT NULL)
        AND is_open = TRUE then t1.position_id end
    ) as total_open_positions_using_interview_feeback
FROM
    (
        select
            *
        from
            public._positions
    ) t1
    LEFT JOIN (
        SELECT
            position_id,
            case when feedback_type = 'quick_feedback' then 'Quick Feedback' when feedback_type = 'interview_feedback' then 'Interview Feedback' else 'Others' end as feedback_type,
            requester_email,
            created_at
        FROM
            public._profile_feedback
        WHERE archive_flag = FALSE
    ) t2 on t1.position_id = t2.position_id

select
    created_at,
    requester_name,
    requester_email,
    reviewer_name,
    reviewer_email,
    manager_of_feedback_requester,
    feedback_type,
    position_id,
    position_title,
    position_location,
    position_location_country,
    position_status,
    position_job_function,
    position_business_unit,
    position_calibration_status,
    position_recruiter_name,
    position_hiring_manager_name,
    position_seniority_level,
    position_is_open,
    position_is_posted,
    position_creation_ts,
    position_is_confidential,
    seniority_level_of_reviewer,
    feedbacks_requested,
    candidate_count,
    feedbacks_submitted,
    feedbacks_pending,
    feedback_response_rate,
    time_to_respond_in_days,
    feedback_forms_used,
    feedbacks_with_follow_up_enabled,
    feedback_submitted_for_followup_enabled,
    time_to_respond_for_followup_enabled_in_mins,
    time_to_respond_for_followup_disabled_in_mins,
    positive_feedbacks,
    negative_feedbacks,
    reviewers_who_give_positive_feedback,
    reviewers_who_give_negative_feedback,
    reviewers_who_respond_after_4_days,
    feedbacks_with_response_after_4_days,
    avg_days_to_hire
from
    public._ta_candidate_feedback_efficiency_and_outcomes t1
    left join (
        select employee_email, manager_name as manager_of_feedback_requester 
        from public._employee
    ) t2
    on lower(trim(t1.requester_email)) = lower(trim(t2.employee_email))

select
    year,
    month,
    start_of_month,
    activity_type,
    count(distinct active_user_id) as active_users_this_month,
    count(activity_timestamp) as feature_usage,
    count(activity_timestamp) / count(distinct active_user_id) as frequency_of_usage_per_user,
    count(
        DISTINCT CASE WHEN (
            DATEDIFF(
                year, previous_activity_timestamp_ts,
                activity_timestamp
            ) = 0
            AND DATEDIFF(
                month, previous_activity_timestamp_ts,
                activity_timestamp
            ) = 1
        )
        OR (
            DATEDIFF(
                year, previous_activity_timestamp_ts,
                activity_timestamp
            ) = 1
            AND DATEDIFF(
                month, previous_activity_timestamp_ts,
                activity_timestamp
            ) = -51
        ) THEN active_user_id END
    ) as repeat_users_following_month,
    count(
        DISTINCT CASE WHEN DATEDIFF(
            month, previous_activity_timestamp_ts,
            activity_timestamp
        ) = 1 THEN active_user_id END
    )* 1.00 / count(distinct active_user_id) as repeat_users_percent
FROM
    (
        SELECT
            datepart('year', timestamp) as year,
            datepart('month', timestamp) as month,
            DATE_TRUNC('month', timestamp) as start_of_month,
            event as activity_type,
            user_id as active_user_id,
            timestamp as activity_timestamp,
            LAG(timestamp) OVER (
                PARTITION BY active_user_id
                ORDER BY
                    timestamp
            ) AS previous_activity_timestamp_ts
        FROM
            public._user_analytics
        where
            (event = 'calibration_confirmed')
            and user_id not like 'import%'
    ) t1
group by
    1,
    2,
    3,
    4

select
    year,
    week,
    start_of_week,
    activity_type,
    count(distinct active_user_id) as active_users_this_week,
    count(activity_timestamp) as feature_usage,
    count(activity_timestamp) / count(distinct active_user_id) as frequency_of_usage_per_user,
    count(
        DISTINCT CASE WHEN (
            DATEDIFF(
                year, previous_activity_timestamp_ts,
                activity_timestamp
            ) = 0
            AND DATEDIFF(
                week, previous_activity_timestamp_ts,
                activity_timestamp
            ) = 1
        )
        OR (
            DATEDIFF(
                year, previous_activity_timestamp_ts,
                activity_timestamp
            ) = 1
            AND DATEDIFF(
                week, previous_activity_timestamp_ts,
                activity_timestamp
            ) = -51
        ) THEN active_user_id END
    ) as repeat_users_following_week,
    count(
        DISTINCT CASE WHEN DATEDIFF(
            week, previous_activity_timestamp_ts,
            activity_timestamp
        ) = 1 THEN active_user_id END
    )* 1.00 / count(distinct active_user_id) as repeat_users_percent
FROM
    (
        SELECT
            datepart('year', timestamp) as year,
            datepart('week', timestamp) as week,
            DATE_TRUNC('week', timestamp) as start_of_week,
            event as activity_type,
            user_id as active_user_id,
            timestamp as activity_timestamp,
            LAG(timestamp) OVER (
                PARTITION BY active_user_id
                ORDER BY
                    timestamp
            ) AS previous_activity_timestamp_ts
        FROM
            public._user_analytics
        where
            (event = 'calibration_confirmed')
            and user_id not like 'import%'
    ) t1
group by
    1,
    2,
    3,
    4

SELECT
    count(distinct t1.profile_id) as total_employees,
    count(
        distinct case when (t2.timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_employees_confirming_calibration,
    count(
        distinct case when is_recruiter then t1.profile_id end
    ) as total_recruiters,
    count(
        distinct case when is_recruiter
        AND (t2.timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_recruiters_confirming_calibration,
    count(
        distinct case when is_hm then t1.profile_id end
    ) as total_hiring_managers,
    count(
        distinct case when is_hm
        AND (t2.timestamp IS NOT NULL) then t1.profile_id end
    ) as total_active_hiring_managers_confirming_calibration,
    count(
        distinct case when (t2.timestamp IS NOT NULL)
        and is_auto_calibrated then t1.profile_id end
    ) as total_active_employees_calibrating_autocalibrated_positions,
    count(
        distinct case when (t2.timestamp IS NOT NULL)
        and is_auto_calibrated then t1.profile_id end
    ) * 1.00 / count(distinct t1.profile_id) as percent_active_employees_calibrating_autocalibrated_positions,
    count(
        distinct case when is_recruiter
        AND (t2.timestamp IS NOT NULL)
        and is_auto_calibrated then t1.profile_id end
    ) as total_active_recruiters_calibrating_autocalibrated_positions,
    count(
        distinct case when is_recruiter
        AND (t2.timestamp IS NOT NULL)
        and is_auto_calibrated then t1.profile_id end
    ) * 1.00 / count(distinct t1.profile_id) as percent_active_recruiters_calibrating_autocalibrated_positions
FROM
    (
        select
            employee_email,
            profile_id,
            hiring_date,
            is_recruiter,
            is_hm
        from
            public._employee
        WHERE
            is_alumni = FALSE
            and employee_id is not null and employee_id != ''
    ) t1
    LEFT JOIN (
        SELECT
            event,
            user_id,
            position_id,
            timestamp
        FROM
            public._user_analytics
        where
            event = 'calibration_confirmed'
        group by
            1,
            2,
            3,
            4
    ) t2 on trim(lower(t1.employee_email)) = trim(lower(t2.user_id))
    LEFT JOIN (
        SELECT
            position_id,
            is_auto_calibrated
        FROM
            public._positions
        group by
            1,
            2
    ) t3 on t2.position_id = t3.position_id

SELECT
    t1.position_id,
    ats_job_id,
    (CASE
        WHEN trim(title) = ''
        OR trim(title) = 'Unknown' THEN 'Title Not Assigned'
        WHEN title IS NULL THEN 'Others'
        ELSE title
    END) AS position_title,
    (CASE
        WHEN trim(LOCATION) = ''
        OR trim(LOCATION) = 'Unknown' THEN 'Location Not Assigned'
        WHEN LOCATION IS NULL THEN 'Others'
        ELSE LOCATION
    END) AS position_location,
    (CASE
        WHEN trim(location_country) = ''
        OR trim(location_country) = 'Unknown' THEN 'Country Not Assigned'
        WHEN location_country IS NULL THEN 'Others'
        ELSE location_country
    END) AS position_location_country,
    (CASE
        WHEN trim(t1.status) = ''
        OR trim(t1.status) = 'Unknown' THEN 'Status Not Assigned'
        WHEN t1.status IS NULL THEN 'Others'
        ELSE t1.status
    END) AS position_status,
    (CASE
        WHEN seniority_level IS NULL THEN 'Others'
        WHEN seniority_level = 'N/A' THEN 'Seniority Level Not Inferred'
        ELSE seniority_level
    END) AS position_inferred_seniority_level,
    [... extensive position and calibration metrics ...]
    concat((has_calibrated_location_indicator + has_calibrated_skills_indicator + has_calibrated_titles_indicator + has_calibrated_similar_cand_indicator + has_calibrated_workexp_indicator)*100/5, '%') AS calibration_percent,
    calibration_json,
    custom_fields_json,
    skills_json,
    metadata_json,
    is_auto_calibrated,
    CASE
        WHEN t3.user_id IS NOT NULL THEN t3.user_id
        ELSE 'calibration confirm not clicked'
    END AS calibration_confirm_user,
    datediff('day', position_creation_ts, confirm_calibration_ts) AS days_taken_to_calibrate,
    t1.time_to_fill_days AS days_to_fill
FROM
    (
        SELECT *,
            CASE
                WHEN has_calibrated_location = 'True' THEN 1
                ELSE 0
            END AS has_calibrated_location_indicator,
            [... additional calibration indicators ...]
        FROM public._positions
    ) t1
    LEFT JOIN [... multiple joins for calibration confirmation, outcomes, and employee details ...]

select
    year,
    month,
    start_of_month,
    activity_type,
    tag_type,
    active_users_this_month,
    feature_usage_count,
    frequency_of_usage_per_user,
    repeat_users_following_month,
    repeat_users_percent
from
    (
        select
            year,
            month,
            start_of_month,
            'profile tags' as activity_type,
            tag_type,
            count(distinct active_user_id) as active_users_this_month,
            count(profile_tag) as feature_usage_count,
            count(profile_tag)/ count(distinct active_user_id) as frequency_of_usage_per_user,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    month, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            ) as repeat_users_following_month,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    month, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            )* 1.00 / count(distinct active_user_id) as repeat_users_percent
        FROM
            (
                select
                    datepart('year', created_at) as year,
                    datepart('month', created_at) as month,
                    DATE_TRUNC('month', created_at) as start_of_month,
                    creator_email as active_user_id,
                    created_at as activity_timestamp,
                    case when tag_type = 'user' then 'Tag added by User' 
                         when tag_type = 'system' then 'Tag added by System' 
                         else 'Others' end as tag_type,
                    profile_tag,
                    LAG(created_at) OVER (
                        PARTITION BY active_user_id, tag_type
                        ORDER BY
                            activity_timestamp
                    ) AS previous_activity_timestamp_ts
                from
                    public._profile_tags
                where creator_email not like 'import%'
            )
        group by
            1,
            2,
            3,
            4,
            5
    )

select
    year,
    week,
    start_of_week,
    activity_type,
    tag_type,
    active_users_this_week,
    feature_usage_count,
    frequency_of_usage_per_user,
    repeat_users_following_week,
    repeat_users_percent
from
    (
        select
            year,
            week,
            start_of_week,
            'profile tags' as activity_type,
            tag_type,
            count(distinct active_user_id) as active_users_this_week,
            count(profile_tag) as feature_usage_count,
            count(profile_tag)/ count(distinct active_user_id) as frequency_of_usage_per_user,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    week, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            ) as repeat_users_following_week,
            count(
                DISTINCT CASE WHEN DATEDIFF(
                    week, previous_activity_timestamp_ts,
                    activity_timestamp
                ) = 1 THEN active_user_id END
            )* 1.00 / count(distinct active_user_id) as repeat_users_percent
        FROM
            (
                select
                    datepart('year', created_at) as year,
                    datepart('week', created_at) as week,
                    DATE_TRUNC('week', created_at) as start_of_week,
                    creator_email as active_user_id,
                    created_at as activity_timestamp,
                    case when tag_type = 'user' then 'Tag added by User' 
                         when tag_type = 'system' then 'Tag added by System' 
                         else 'Others' end as tag_type,
                    profile_tag,
                    LAG(created_at) OVER (
                        PARTITION BY active_user_id, tag_type
                        ORDER BY
                            activity_timestamp
                    ) AS previous_activity_timestamp_ts
                from
                    public._profile_tags
                where creator_email not like 'import%'
            )
        group by
            1,
            2,
            3,
            4,
            5
    ) t2

SELECT
    count(distinct t1.profile_id) as total_employees,
    count(
        distinct case when (t2.creator_email IS NOT NULL) then t1.profile_id end
    ) as total_active_employees,
    count(
        distinct case when is_recruiter then t1.profile_id end
    ) as total_recruiters,
    count(
        distinct case when is_recruiter
        AND t2.creator_email IS NOT NULL then t1.profile_id end
    ) as total_active_recuiters,
    count(
        distinct case when is_sourcer then t1.profile_id end
    ) as total_sourcers,
    count(
        distinct case when is_sourcer
        AND (t2.creator_email IS NOT NULL) then t1.profile_id end
    ) as total_active_sourcers
FROM
    (
        select
            employee_email,
            profile_id,
            is_recruiter,
            is_sourcer
        from
            public._employee
        WHERE
            is_alumni = FALSE
            and employee_id is not null and employee_id != ''
    ) t1
    LEFT JOIN (
        SELECT
            distinct creator_email
        FROM
            public._profile_tags
        where
            tag_type = 'user'
    ) t2 on trim(
        lower(t1.employee_email)
    ) = trim(
        lower(t2.creator_email)
    )

SELECT
    t1.profile_id,
    candidate_name,
    profile_title as candidate_current_title,
    profile_email as candidate_email,
    t1.profile_tag,
    t1.creator_email,
    t1.created_at,
    t1.position_id,
    case when tag_type = 'user' then 'Tag added by User' 
         when tag_type = 'system' then 'Tag added by System' 
         else 'Others' end as tag_type,
    new_applicant_indicator_any_position,
    phonescreen_indicator_any_position,
    onsite_interview_indicator_any_position,
    offer_indicator_any_position,
    hired_indicator_any_position,
    manager_name as manager_of_profile_tag_creator
FROM
    (
        select
            *
        from
            public._profile_tags
    ) t1
    left join (
        select
            profile_id,
            max(
                case when stage_group = 'new_applicant' then 1 else 0 end
            ) as new_applicant_indicator_any_position,
            max(
                case when stage_group = 'phonescreen' then 1 else 0 end
            ) as phonescreen_indicator_any_position,
            max(
                case when stage_group IN ('onsite', 'interview') then 1 else 0 end
            ) as onsite_interview_indicator_any_position,
            max(
                case when stage_group = 'offer' then 1 else 0 end
            ) as offer_indicator_any_position,
            max(
                case when application_hired_ts is NOT NULL then 1 else 0 end
            ) as hired_indicator_any_position
        FROM
            public._ta_leaders_hiring_outcomes
        where
            stage_group IN (
                'new_applicant', 'phonescreen', 'onsite',
                'interview', 'offer')
            OR application_hired_ts is NOT NULL
        group by
            1
    ) t2 on t1.profile_id = t2.profile_id
    left join (
        select
            employee_email,
            manager_name
        from
            public._employee
    ) t3 on trim(
        lower(t1.creator_email)
    ) = trim(
        lower(t3.employee_email)
    )
    left join (
        select
            profile_id,
            concat(
                first_name,
                concat(' ', last_name)
            ) as candidate_name,
            title as profile_title,
            primary_email as profile_email
        from
            public._profile
    ) t4 on t1.profile_id = t4.profile_id

select
    position_id,
    title,
    location,
    location_country,
    status,
    is_open,
    is_posted,
    creation_ts,
    seniority_level,
    job_function,
    business_unit,
    ats_job_id,
    system_id,
    employment_type,
    position_seniority,
    pay_rate_type,
    is_confidential,
    number_of_openings,
    calibration_status,
    position_type,
    supervisory_org,
    is_priority,
    hiring_manager,
    recruiter,
    recruiter_name,
    hiring_manager_name,
    job_code,
    position_name,
    hiring_company
from
    public._positions

select
    year,
    month,
    start_of_month,
    count(distinct active_user_id) as active_users_this_month,
    count(activity_timestamp) as feature_usage_count,
    count(activity_timestamp)/ count(distinct active_user_id) as frequency_of_usage_per_user,
    count(
        DISTINCT CASE WHEN DATEDIFF(
            month, previous_activity_timestamp_ts,
            activity_timestamp
        ) = 1 THEN active_user_id END
    ) as repeat_users_following_month,
    count(
        DISTINCT CASE WHEN DATEDIFF(
            month, previous_activity_timestamp_ts,
            activity_timestamp
        ) = 1 THEN active_user_id END
    )* 1.00 / count(distinct active_user_id) as repeat_users_percent
FROM
    (
        select
            datepart('year', timestamp) as year,
            datepart('month', timestamp) as month,
            DATE_TRUNC('month', timestamp) as start_of_month,
            user_id as active_user_id,
            timestamp as activity_timestamp,
            LAG(timestamp) OVER (
                PARTITION BY active_user_id
                ORDER BY
                    activity_timestamp
            ) AS previous_activity_timestamp_ts
        from
            (
                SELECT
                    user_id,
                    timestamp
                FROM
                    (
                        select
                            group_id,
                            user_id,
                            position_id,
                            timestamp
                        from
                            public._user_analytics
                        where user_id not like 'import%'
                        group by
                            1,
                            2,
                            3,
                            4
                    ) t1
                    INNER JOIN (
                        select
                            *
                        from
                            public._positions
                        where
                            system_id = 'sourcing'
                    ) t2 ON t1.position_id = t2.position_id AND t1.group_id = t2.group_id
            )
    ) t1
group by
    1,
    2,
    3

select
    year,
    week,
    start_of_week,
    count(distinct active_user_id) as active_users_this_week,
    count(activity_timestamp) as feature_usage_count,
    count(activity_timestamp)/ count(distinct active_user_id) as frequency_of_usage_per_user,
    count(
        DISTINCT CASE WHEN DATEDIFF(
            week, previous_activity_timestamp_ts,
            activity_timestamp
        ) = 1 THEN active_user_id END
    ) as repeat_users_following_week,
    count(
        DISTINCT CASE WHEN DATEDIFF(
            week, previous_activity_timestamp_ts,
            activity_timestamp
        ) = 1 THEN active_user_id END
    )* 1.00 / count(distinct active_user_id) as repeat_users_percent
FROM
    (
        select
            datepart('year', timestamp) as year,
            datepart('week', timestamp) as week,
            DATE_TRUNC('week', timestamp) as start_of_week,
            user_id as active_user_id,
            timestamp as activity_timestamp,
            LAG(timestamp) OVER (
                PARTITION BY active_user_id
                ORDER BY
                    activity_timestamp
            ) AS previous_activity_timestamp_ts
        from
            (
                SELECT
                    user_id,
                    timestamp
                FROM
                    (
                        select
                            group_id,
                            user_id,
                            position_id,
                            timestamp
                        from
                            public._user_analytics
                        where user_id not like 'import%'
                        group by
                            1,
                            2,
                            3,
                            4
                    ) t1
                    INNER JOIN (
                        select
                            *
                        from
                            public._positions
                        where
                            system_id = 'sourcing'
                    ) t2 ON t1.position_id = t2.position_id AND t1.group_id = t2.group_id
            )
    ) t1
group by
    1,
    2,
    3

SELECT
    count(distinct t1.profile_id) as total_employees,
    count(
        distinct case when (t2.user_id IS NOT NULL) then t1.profile_id end
    ) as total_active_employees,
    count(
        distinct case when is_recruiter then t1.profile_id end
    ) as total_recruiters,
    count(
        distinct case when is_recruiter
        AND t2.user_id IS NOT NULL then t1.profile_id end
    ) as total_active_recuiters,
    count(
        distinct case when is_sourcer then t1.profile_id end
    ) as total_sourcers,
    count(
        distinct case when is_sourcer
        AND (t2.user_id IS NOT NULL) then t1.profile_id end
    ) as total_active_sourcers
FROM
    (
        select
            employee_email,
            profile_id,
            is_recruiter,
            is_sourcer
        from
            public._employee
        WHERE
            is_alumni = FALSE
            and employee_id is not null and employee_id != ''
    ) t1
    LEFT JOIN (
        SELECT
            distinct user_id
        FROM
            (
                select
                    user_id,
                    position_id
                from
                    public._user_analytics
                group by
                    1,
                    2
            ) t1
            INNER JOIN (
                select
                    *
                from
                    public._positions
                where
                    system_id = 'sourcing'
            ) t2 ON t1.position_id = t2.position_id
    ) t2 on trim(
        lower(t1.employee_email)
    ) = trim(
        lower(t2.user_id)
    )

select 
event,
user_id,
t1.position_id as user_analytics_position_id,
source,
timestamp as activity_timestamp, 
num,
t1.profile_id,
entity_id,
t2.*,
t3.*,
case when is_recruiter then 'Yes' else 'No' end as recruiter_role_indicator,
case when is_hm then 'Yes' else 'No' end as hiring_manager_role_indicator,
case when is_sourcer then 'Yes' else 'No' end as sourcer_role_indicator
from public._user_analytics t1
inner join (select * from public._positions where system_id ='sourcing') t2
on t1.position_id = t2.position_id
left join (
select
employee_email,
manager_name as manager_of_active_community_user,
concat(
first_name,
concat(' ', last_name)
) as employee_name,
case when title IS NULL then 'Title not assigned' else title end as employee_title,
case when level IS NULL then 'Level not assigned' else level end as employee_level,
case when seniority IS NULL then 'Seniority not assigned' else seniority end as employee_seniority,
case when lob IS NULL then 'Level of Business not assigned' else lob end as employee_line_of_business,
case when business_unit IS NULL then 'Business unit not assigned' else business_unit end as employee_business_unit,
is_alumni,
case when location IS NULL then 'Location not found' else location end as employee_location,
case when location_country IS NULL then 'Location Country not found' else location end as employee_location_country,
is_recruiter,
is_hm,
is_sourcer,
is_event_team_external
from public._employee
)t3
on lower(trim(t1.user_id)) = lower(trim(t3.employee_email))

select
    t1.profile_id,
    application_id,
    application_source_type,
    application_source_name,
    application_ts,
    application_hired_ts,
    ats_job_id,
    position_id,
    application_match_score,
    candidate_id,
    application_inferred_gender as ef_gender,
    application_inferred_race as ef_race,
    application_is_referral as is_referral,
    application_is_internal as is_internal,
    application_is_career_site as application_is_career_site,
    application_in_talent_network as in_talent_network,
    application_status,
    system_id,
    utm_source,
    utm_campaign,
    application_last_stage as last_stage,
    application_last_stage_group as last_stage_group,
    application_last_stage_ts as last_stage_ts,
    application_rejection_reason,
    stage_group,
    stage,
    stage_ts,
    stage_group_index,
    ef_stage_group,
    stage_group_display_name,
    position_title as title,
    position_location as location,
    position_location_country as location_country,
    position_status as status,
    position_inferred_seniority_level as seniority_level,
    position_job_function as job_function,
    position_business_unit as business_unit,
    position_employment_type as employment_type,
    position_calibration_status as calibration_status,
    position_type,
    position_recruiter,
    position_hiring_manager,
    position_recruiter_name,
    position_hiring_manager_name,
    position_supervisory_org,
    position_is_open as is_open,
    position_is_posted as is_posted,
    position_creation_ts as creation_ts,
    position_is_confidential as is_confidential,
    number_of_openings,
    position_earliest_opened_at,
    position_pay_rate_type as pay_rate_type,
    candidate_self_declared_gender as gender,
    candidate_self_declared_race as race,
    concat(
        candidate_first_name,
        concat(' ', candidate_last_name)
    ) as candidate_name,
    candidate_email,
    enc_profile_id
from
    public._community_applications_and_stages t1
    left join
    (
        select
            profile_id,
            primary_email as candidate_email,
            enc_profile_id
        from public._profile
    ) t2
    on t1.profile_id = t2.profile_id

select
    planned_event_id,
    profile_id,
    position_id,
    activity_type,
    created_by,
    updated_by,
    candidate_activity_created_at,
    event_name,
    event_start_timestamp,
    event_end_timestamp,
    event_type,
    event_status,
    event_creator_email,
    event_created_at,
    profile_system_id,
    candidate_first_name,
    candidate_last_name,
    candidate_inferred_gender,
    candidate_current_title,
    candidate_id,
    candidate_location_country,
    candidate_current_company,
    candidate_exp_years,
    candidate_highest_degree,
    candidate_latest_major,
    candidate_inferred_race,
    candidate_seniority_level,
    event_location_type,
    institution,
    venue,
    is_cancelled,
    is_published,
    event_custom_fields_json
from public._recruiting_events

SELECT
    t1.project_id,
    t2.profile_id,
    t2.application_index,
    t2.is_application_active,
    t2.user_profile_id,
    t2.stage_value,
    t2.stage_ts,
    t2.stage_rank,
    t1.title as project_title,
    t1.status as project_status,
    t1.created_by as project_creator,
    t1.creation_ts as project_creation_ts,
    t1.completed_ts as project_completed_ts,
    t1.end_ts as project_end_ts,
    t1.number_of_project_managers,
    t1.number_of_team_members,
    t1.total_hours as project_total_hours,
    t3.employee_id,
    t3.business_unit as employee_business_unit,
    t3.lob as employee_lob,
    t3.is_alumni as employee_is_alumni,
    t3.location_country as employee_location_country,
    t3.location as employee_location,
    t3.seniority as employee_seniority,
    t3.first_name as employee_first_name,
    t3.last_name as employee_last_name,
    t3.ef_gender as employee_ef_gender,
    t3.ef_race as employee_ef_race,
    t3.employee_email,
    t4.employee_id as project_creator_employee_id,
    t4.business_unit as project_creator_business_unit,
    t4.lob as project_creator_lob,
    t4.is_alumni as project_creator_is_alumni,
    t4.location_country as project_creator_location_country,
    t4.location as project_creator_location,
    t4.first_name as project_creator_first_name,
    t4.last_name as project_creator_last_name
FROM
    public._project t1
    LEFT JOIN public._project_application_stages t2 ON t1.project_id = t2.project_id
    LEFT JOIN public._employee t3 ON t2.profile_id = t3.profile_id
    LEFT JOIN public._employee t4 ON lower(t1.created_by) = lower(t4.employee_email)