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.
This approach involves utilizing SQL queries to select the primary department for each employee effectively. The idea is to select the department_id directly if the primary_flag is 'Y'. For cases with a single department, we forcefully set the primary_flag to 'N' but choose that department.
This solution makes use of a subquery to determine the employees who belong to only one department. We use a simple GROUP BY and HAVING clause to get such employees. The WHERE clause merges this subquery result with employees who have their primary_flag set to 'Y'.
Time Complexity: O(n), where n is the number of records in the Employee table. The query involves a single pass through the data for filtering and an additional pass for the group operation.
Space Complexity: O(n) for storing intermediate results during grouping.
This approach leverages SQL subqueries to determine if an employee belongs to only one department and selects the sole department as the primary. For employees with more than one department, the department marked with 'Y' is considered primary.
This query uses a CASE statement to differentiate between employees with multiple departments and those with a single one. When there are multiple departments, the query picks the one with primary_flag = 'Y'. For employees with only one department, it simply selects that department as primary.
Time Complexity: O(n), where n is the number of records in the Employee table. The inner subquery is efficient due to indexing and specific filtering.
Space Complexity: O(1) if indexing is used on employee_id and department_id.
This approach involves determining whether an employee has multiple departments or a single department by using aggregation. We can group the records by employee_id and use a COUNT function to find out the number of departments each employee belongs to. If an employee only belongs to one department, we select it regardless of the primary_flag. Otherwise, we pick the department where primary_flag is 'Y'.
This SQL query uses a CASE statement, aggregation, and conditional max to determine which department an employee belongs to primarily. If the employee belongs to just one department, that department_id is selected. Else, it chooses the department with 'Y' as primary_flag.
Time Complexity: O(N), where N is the number of rows in the Employee table. This is due to the need to scan through all rows once for the aggregation.
Space Complexity: O(M), where M is the number of unique employee_ids due to the storage required for the result set.
This approach requires joining the Employee table with itself to identify employees with multiple department entries. We select the department where the primary_flag is 'Y'. For cases where all entries for an employee have primary_flag as 'N', the logic select the minimal department_id. This approach might involve more steps compared to the aggregation method but helps emphasize filtering techniques.
This SQL query performs a self-join on the Employee table to identify employees' primary departments. If no primary department is specified via the primary_flag, it defaults to any (here the minimal) department_id available for those employees. This takes advantage of the LEFT JOIN and COALESCE to ensure all employees are returned.
Time Complexity: O(N^2), where N is the number of rows in the Employee table due to the self-join operation.
Space Complexity: O(N) for storing results where N is the number of unique employee_ids.
| Approach | Complexity |
|---|---|
| Using SQL with Conditional Selection | Time Complexity: O(n), where n is the number of records in the Employee table. The query involves a single pass through the data for filtering and an additional pass for the group operation. |
| Using SQL with Subqueries | Time Complexity: O(n), where n is the number of records in the Employee table. The inner subquery is efficient due to indexing and specific filtering. |
| Approach 1: Aggregation and Conditional Selection | Time Complexity: O(N), where N is the number of rows in the Employee table. This is due to the need to scan through all rows once for the aggregation. Space Complexity: O(M), where M is the number of unique employee_ids due to the storage required for the result set. |
| Approach 2: Self-Join and Filtering | Time Complexity: O(N^2), where N is the number of rows in the Employee table due to the self-join operation. Space Complexity: O(N) for storing results where N is the number of unique employee_ids. |
LeetCode 1789 Facebook/Meta Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 5,552 views views
Watch 9 more video solutions →Practice Primary Department for Each Employee with our built-in code editor and test cases.
Practice on FleetCode