Software Voices

A popular interview question for SQL Developers goes something like the following: “Given an employee table where ManagerID is an attribute of the Employee record, write a stored procedure which counts the total number of employees which report to the passed employee.”

For clarity I will point out that the organization is a tree where each employee represents a node.

There is a simple answer which you should probably stick to in the context of an interview. A top down approach (without the sproc wrapper) would look like this:At this point you can be confident that the number of records in the #tEmployeeTree table represents the correct answer.

Now, there is an embellishment to this question which can lead to some interesting discussion. “An executive would like to know which of his or her direct subordinates has the most number of employees in their organization.”