Query to get dept ids

See more:

SQL

interview

aggregation

Hi Guys ,

Today I went to Interview for SQL developer.He asked me one question ,after long struggle to gave up my self from there

lets talk about query
I have Employee table

empId  ename   deptId
1      P       10
2      Q       10
3      X       20
4      Y       20
5      Z       20
6      P1      10
7      Q1      10
8      X1      20
9      Y1      20
10     Z1      20
11     m       30

and Certification table

certId  empid
1        1
2        3
3        5
4       11

Now he wants the dept Id on which doesn't have 50% of certification done by their employees

output :

Dept ID 
10
20 

I tried by with below query

Select a.deptID from
(Select COUNT(*)as  total ,deptId
 from #employee
Group by deptId) A
join
 (Select COUNT(*) as deptwise ,deptId
                        from  #employee emp left join #certifiaction cert
                        on  cert.empid=emp.empid
                        where  cert.certId is null
                        group by emp.deptId) B

ON A.deptId=B.deptId
where (b.deptwise*0.1) /(a.total*0.1)>0.5

But he doesn't want this query.How to do that with out doing derived tables?

Please help me to resolve ,will help for another interview .


Thanks
Pratap