Primary Department For Each Employee - Leetcode Solution

Problem Statement:

The problem "Primary Department for Each Employee" on leetcode is as follows:

You have a table Employee, which contains employee information, such as their name and department they belong to. This table contains the following columns: -id: the id of the employee. -name: the name of the employee. -department: the department the employee belongs to.

Write an SQL query to find the primary department for each employee. The primary department is defined as the department where the employee has the most number of followers. For example, assume we have the following table:

Employee

• id | name | department
• ---|-------|------------
• 1 | Joe | Engineering
• 2 | Henry | Engineering
• 3 | Sam | Sales
• 4 | Max | Sales

In this example, Joe and Henry are in the Engineering department, and Sam and Max are in the Sales department. However, Joe has more followers in the Sales department than in Engineering department and hence Joe's primary department would be Sales.

Solution: The solution to the problem "Primary Department for Each Employee" is given below:

To find the primary department for each employee, we need to count the number of followers for each department. Then, we need to find the department with the maximum followers for each employee.

To do this, we can use the following SQL query:

SELECT id, name, (SELECT department FROM ( SELECT department, COUNT(*) AS followers FROM Employee GROUP BY department ) e2 WHERE e1.id = e2.id ORDER BY followers DESC LIMIT 1 ) AS primary_department FROM Employee e1;

Explanation:

We use a subquery to count the followers for each department and then use another subquery to find the department with the maximum followers for each employee. The rest of the fields are also selected.

In the subquery, we use the GROUP BY clause to group the records by department and COUNT(*) to count the followers in each department.

Then, we use the ORDER BY clause to order the results by followers in descending order and LIMIT 1 to select the first record.

In the outer query, we join the results with the Employee table based on the employee id and select the required fields.

Finally, we get the primary department for each employee.

This solution solves the problem "Primary Department for Each Employee" on leetcode.

1