NTL-HRR

SELECT
	SUM(CASE
        WHEN jasm.is_first_stage = TRUE AND status.is_success IS NULL THEN 1
        ELSE 0
    END) AS new_count,
    SUM(CASE
        WHEN jasm.is_final_stage = TRUE AND status.is_success = TRUE THEN 1
        WHEN status.is_success = FALSE THEN 1
        ELSE 0
    END) AS completed_count,
    SUM(CASE
        WHEN jasm.is_final_stage = FALSE AND status.is_success = TRUE THEN 1
		WHEN jasm.is_first_stage = FALSE AND status.is_success IS NULL THEN 1
        ELSE 0
    END) AS in_progress_count
FROM job_application ja
JOIN job_app_stage_master jasm ON ja.stage_id = jasm.stage_id
JOIN job_app_status_master status ON ja.status_id = status.status_id
WHERE ja.job_app_id = 'xxxx';