SQL Puzzle | Employees count in Active Departments Puzzle | Frequent SQL SERVER Interview Question

In this puzzle you have to find count of employees present in each active departments. Please check the sample input and the expected output. The challenge is to do this in a single select.

Note – Few of my colleagues frequently asks this question in technical interviews.

Sample Input

Employee Table

EmpId Name DeptId
1 Pawan 1
2 Ramesh 2
3 Krishan 1

Departments TABLE

DeptId DeptName IsActive
1 A 1
2 B 1
3 C 1
4 D 0

Expected Output

DeptName Active countof emp
A 1 2
B 1 1
C 1 1

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE Departments ( DeptId INT ,DeptName VARCHAR(10) ,IsActive INT ) GO INSERT INTO Departments VALUES (1 , 'A' ,1 ), (2 , 'B' ,1 ), (3 , 'C' ,1 ), (4 , 'D' ,0 ) GO CREATE TABLE Emps ( EmpId INT ,[Name] VARCHAR(10) ,DeptId INT ) GO INSERT INTO Emps VALUES (1,'Pawan',1), (2,'Ramesh',2), (3,'Krishan',1) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

SOLUTION – 1 | This solution was given by my friend ASHUTOSH

 ```-- SELECT D.DeptName , 1 Active , COUNT(E.EmpId) [countof emp] FROM Departments D LEFT JOIN Emps E ON D.DeptId = E.DeptId WHERE D.IsActive = 1 GROUP BY D.DeptName -- ```

Output-1

 ```-- DeptName Active countof emp ---------- ----------- ----------- A 1 2 B 1 1 C 1 0 (3 rows affected) -- ```

SOLUTION – 2

 ```-- SELECT DISTINCT D.DeptName , 1 Active , COUNT(E.EmpId) OVER(PARTITION BY D.DeptName) [countof emp] FROM Departments D LEFT JOIN Emps E ON D.DeptId = E.DeptId WHERE D.IsActive = 1 -- ```

Output-2

 ```-- DeptName Active countof emp ---------- ----------- ----------- A 1 2 B 1 1 C 1 0 (3 rows affected) -- ```

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/