Try this:
WITH CTE0 (deptid, deptcount)
AS
(
select e1.deptid, count(c.empid)
from employee e1 join certificate c on e1.empid = c.empid
group by e1.deptid
),
CTE1 (deptid, deptcount)
AS
(
select deptid, count(empid)
from employee
group by deptid
)
SELECT CTE1.deptid FROM cte0 join cte1 ON CTE0.deptid=CTE1.deptid WHERE
CTE0.deptcount / CTE1.deptcount < 0.5
refer:
Common Table Expressions(CTE) in SQL SERVER 2008[
^]