Total first time job performers between dates

first_time_job_performers.sql

-- First time job performers between dates
SELECT user_performed_in_range.user_id FROM (
    SELECT 
        users.id as user_id,
        COUNT(users.id) as user_performed_jobs_count
    FROM "jobs"
    INNER JOIN job_users on job_users.job_id = jobs.id AND job_users.will_perform = true
    INNER JOIN users on job_users.user_id = users.id
    WHERE (job_end_date >= {start_time} AND job_end_date <= {end_time})
        AND cancelled = false
        AND filled = true
        AND staffing_job = false
        AND direct_recruitment_job = false
    GROUP BY users.id
    ORDER BY users.id
) as user_performed_in_range
LEFT OUTER JOIN (
    SELECT users.id AS user_id FROM users
    LEFT JOIN job_users ON job_users.user_id = users.id
    INNER JOIN jobs ON job_users.job_id = jobs.id
    AND jobs.job_end_date < {start_time}
    AND jobs.cancelled = FALSE
    AND jobs.staffing_job = FALSE
    WHERE job_users.will_perform = TRUE
        AND users.company_id IS NULL
    GROUP BY users.id
    ORDER BY users.id
) as users_with_previous_jobs ON user_performed_in_range.user_id = users_with_previous_jobs.user_id
WHERE users_with_previous_jobs.user_id IS NULL

total_first_time_job_performers.sql

-- Total first time job performers between dates

SELECT COUNT(user_performed_in_range.user_id) FROM (
    SELECT 
        users.id as user_id,
        COUNT(users.id) as user_performed_jobs_count
    FROM "jobs"
    INNER JOIN job_users on job_users.job_id = jobs.id AND job_users.will_perform = true
    INNER JOIN users on job_users.user_id = users.id
    WHERE (job_end_date >= {start_time} AND job_end_date <= {end_time})
        AND cancelled = false
        AND filled = true
        AND staffing_job = false
        AND direct_recruitment_job = false
    GROUP BY users.id
    ORDER BY users.id
) as user_performed_in_range
LEFT OUTER JOIN (
    SELECT users.id AS user_id FROM users
    LEFT JOIN job_users ON job_users.user_id = users.id
    INNER JOIN jobs ON job_users.job_id = jobs.id
    AND jobs.job_end_date < {start_time}
    AND jobs.cancelled = FALSE
    AND jobs.staffing_job = FALSE
    WHERE job_users.will_perform = TRUE
        AND users.company_id IS NULL
    GROUP BY users.id
    ORDER BY users.id
) as users_with_previous_jobs ON user_performed_in_range.user_id = users_with_previous_jobs.user_id
WHERE users_with_previous_jobs.user_id IS NULL