SQL problem - Primary Department for Each Employee

Problem

Query

(
    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(department_id) = 1
)

Editorial Solution

Approach 1: UNION

-- Retrieving employees with primary_flag set to 'Y'
SELECT 
  employee_id, 
  department_id 
FROM 
  Employee 
WHERE 
  primary_flag = 'Y' 
UNION 
-- Retrieving employees that appear exactly once in the Employee table
SELECT 
  employee_id, 
  department_id 
FROM 
  Employee 
GROUP BY 
  employee_id 
HAVING 
  COUNT(employee_id) = 1;

Approach 2: Window Function (COUNT)

Step 1 - Inner Query with Window Function:

SELECT 
  *, 
  COUNT(employee_id) OVER(PARTITION BY employee_id) AS EmployeeCount 
FROM 
  Employee

Result:

employee_id	department_id	primary_flag	EmployeeCount
1	1	N	1
2	1	Y	2
2	2	N	2
3	3	N	1
4	2	N	3
4	3	Y	3
4	4	N	3

Note:

• COUNT(employee_id) OVER(PARTITION BY employee_id) is a window function
  • PARTITION BY employee_id: This breaks down the data into ‘windows’ or ‘partitions’ of rows that have the same employee_id. Each window is essentially a subset of the data for a specific employee.
  • COUNT(employee_id) OVER(...): This counts the number of rows (i.e., the number of departments) for each employee within their respective partition/window. The result is a new column, EmployeeCount, which tells us how many departments each employee is associated with. This count is repeated for every row of the same employee.

Step 2 - Alias & Outer Query:

SELECT 
  employee_id, 
  department_id 
FROM 
  EmployeePartition 

Step 3 - Filtering with WHERE Clause:

WHERE 
  EmployeeCount = 1 
  OR primary_flag = 'Y'

Final implementation:

SELECT 
  employee_id, 
  department_id 
FROM 
  (
    SELECT 
      *, 
      COUNT(employee_id) OVER(PARTITION BY employee_id) AS EmployeeCount
    FROM 
      Employee
  ) EmployeePartition 
WHERE 
  EmployeeCount = 1 
  OR primary_flag = 'Y';