Cacher is the code snippet organizer for pro developers

select subrole.job_id
  , sum(case when subrole.core = 'Core' then 1 else 0 end) as core_count
  , sum(case when subrole.core = 'Non-Core' then 1 else 0 end) as non_core_count
from (
    select subroles.job_id
      , subroles.subrole_id
      , subroles.subrole_input_type
      , roles.name as subrole
      , case when roles.core = true then 'Core'
          when roles.core = false then 'Non-Core'
          else null
        end as core
    from (
    	select subroles_crossed.job_id as job_id
        	, case when subroles_explicit.job_id::numeric is not null then subroles_crossed.explicit_subrole::numeric
              else subroles_crossed.inferred_subrole::numeric
              end as subrole_id
            , case when subroles_explicit.job_id is not null then 'explicit'
              else 'inferred'
                end as subrole_input_type
		from (
			select case when s.job_id::numeric is null then j.job_id::numeric
			  else s.job_id::numeric
			  end as job_id
				, j.role_id:: numeric as explicit_subrole
				, s.subrole_id::numeric as inferred_subrole
			from jobs
			left join jobs_roles j -- explicit
				on jobs.id= j.job_id
			left join jobs_with_subroles_using_skills s -- implicit
				on jobs.id = s.job_id
		) as subroles_crossed
		left join (
			select job_id
				, count(*)
			from jobs_roles
			group by 1
		) as subroles_explicit
			on subroles_crossed.job_id = subroles_explicit.job_id
    ) as subroles
    left join roles
      on subroles.subrole_id = roles.id
    group by 1,2,3,4,5
) as subrole
group by 1