{x}
blog image

Primary Department for Each Employee

Table: Employee

+---------------+---------+
| Column Name   |  Type   |
+---------------+---------+
| employee_id   | int     |
| department_id | int     |
| primary_flag  | varchar |
+---------------+---------+
(employee_id, department_id) is the primary key (combination of columns with unique values) for this table.
employee_id is the id of the employee.
department_id is the id of the department to which the employee belongs.
primary_flag is an ENUM (category) of type ('Y', 'N'). If the flag is 'Y', the department is the primary department for the employee. If the flag is 'N', the department is not the primary.

 

Employees can belong to multiple departments. When the employee joins other departments, they need to decide which department is their primary department. Note that when an employee belongs to only one department, their primary column is 'N'.

Write a solution to report all the employees with their primary department. For employees who belong to one department, report their only department.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+-------------+---------------+--------------+
| employee_id | department_id | primary_flag |
+-------------+---------------+--------------+
| 1           | 1             | N            |
| 2           | 1             | Y            |
| 2           | 2             | N            |
| 3           | 3             | N            |
| 4           | 2             | N            |
| 4           | 3             | Y            |
| 4           | 4             | N            |
+-------------+---------------+--------------+
Output: 
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |
| 4           | 3             |
+-------------+---------------+
Explanation: 
- The Primary department for employee 1 is 1.
- The Primary department for employee 2 is 1.
- The Primary department for employee 3 is 3.
- The Primary department for employee 4 is 3.

Solution Explanation:

The problem requires finding the primary department for each employee. An employee can belong to multiple departments, but only one is marked as primary ('Y' in primary_flag). If an employee only belongs to a single department, that department is considered primary.

Approach:

The solution uses a UNION of two SELECT statements to achieve the desired result.

  1. Primary Department Selection: The first SELECT statement retrieves employees and their department IDs where primary_flag = 'Y'. This directly selects the primary department for employees who have multiple departments.

  2. Single Department Selection: The second SELECT statement retrieves employees who belong to only one department. It groups the results by employee_id and uses the HAVING clause to filter for groups with a count of 1 (meaning only one department).

The UNION combines the results of these two queries, eliminating duplicate rows. The final result includes all employees and their primary departments, as defined by the problem's conditions.

Time Complexity Analysis:

The time complexity of this solution is dominated by the two SELECT statements. The first statement is O(N), where N is the number of rows in the Employee table, as it scans through the table once to find rows matching primary_flag = 'Y'. The second statement involves a GROUP BY and HAVING clause, which typically have a time complexity of O(N log N) or O(N) depending on the database system's implementation of these operations. However, since we're filtering for groups of size 1, it essentially behaves as a linear scan in most database systems.

Therefore, the overall time complexity of this solution is approximately O(N), where N is the number of rows in the Employee table.

Space Complexity Analysis:

The space complexity is determined by the size of the intermediate results generated by the two SELECT statements and the final UNION operation. In the worst case, both intermediate results could be of size O(N), so the space complexity is approximately O(N).

Code in MySQL:

SELECT employee_id, department_id
FROM Employee
WHERE primary_flag = 'Y'
UNION
SELECT employee_id, department_id
FROM Employee
GROUP BY employee_id
HAVING COUNT(*) = 1;

The code is already provided in the problem description. I've only made a minor correction to the HAVING clause to use COUNT(*) which is a more standard way to count rows in a group. Both COUNT(1) and COUNT(*) work functionally identically in this context.

No other languages were requested, but this solution is database-specific and would need to be adapted significantly for other languages and contexts. The core idea of using two separate queries and a UNION remains the same, however.