Primary Department for Each Employee - Problem

You are given an Employee table that tracks which departments employees belong to and whether each department is their primary department.

Key Rules:

  • Employees can belong to multiple departments
  • When an employee belongs to multiple departments, exactly one has primary_flag = 'Y'
  • When an employee belongs to only one department, that department has primary_flag = 'N'

Write a SQL query to find each employee's primary department. For employees with multiple departments, return the one marked as primary. For employees with only one department, return that department.

Table Schema

Employee
Column Name Type Description
employee_id PK int Unique identifier for the employee
department_id PK int Identifier for the department
primary_flag varchar Either 'Y' (primary) or 'N' (not primary)
Primary Key: (employee_id, department_id)
Note: Composite primary key allows employees to belong to multiple departments

Input & Output

Example 1 — Mixed Single and Multiple Departments
Input 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
💡 Note:

Employee 1: Only in department 1, so return department 1 (even though primary_flag='N')

Employee 2: In departments 1 and 2, department 1 has primary_flag='Y', so return department 1

Employee 3: Only in department 3, so return department 3

Employee 4: In departments 2, 3, and 4, department 3 has primary_flag='Y', so return department 3
Example 2 — All Single Department Employees
Input Table:
employee_id department_id primary_flag
1 1 N
2 2 N
3 3 N
Output:
employee_id department_id
1 1
2 2
3 3
💡 Note:

All employees belong to only one department each. Even though all have primary_flag='N', we return their single department as their primary department.
Example 3 — All Multi-Department Employees
Input Table:
employee_id department_id primary_flag
1 1 Y
1 2 N
2 2 N
2 3 Y
Output:
employee_id department_id
1 1
2 3
💡 Note:

Both employees belong to multiple departments. Employee 1's primary is department 1, Employee 2's primary is department 3 (marked with primary_flag='Y').

Constraints

  • 1 ≤ employee_id ≤ 100
  • 1 ≤ department_id ≤ 100
  • primary_flag is either 'Y' or 'N'
  • Each employee has at most one department with primary_flag = 'Y'
  • Employees with only one department always have primary_flag = 'N'

Visualization

Tap to expand
Primary Department for Each Employee INPUT emp_id dept_id primary 1 Sales N 1 Eng Y 2 Sales N 3 Eng N 3 Sales Y 3 HR N Employee Groups: Emp 1 2 depts Emp 2 1 dept Emp 3 3 depts Multiple depts Single dept ALGORITHM STEPS 1 Count Departments Group by employee_id COUNT(*) OVER (PARTITION BY employee_id) 2 Check Conditions Apply selection logic dept_count = 1? YES:Keep NO:Check Y 3 Filter Primary primary_flag = 'Y' for multi WHERE dept_count = 1 OR primary_flag = 'Y' 4 Return Result Select emp_id, dept_id FINAL RESULT employee_id dept 1 1 Eng 2 2 Sales 3 3 Sales Emp 1: Has 2 depts Eng selected (primary_flag=Y) Sales ignored (primary_flag=N) Emp 2: Has 1 dept only Sales kept (even with flag=N) Emp 3: Has 3 depts Sales selected (primary_flag=Y) Eng, HR ignored Key Insight: Use window function COUNT(*) OVER (PARTITION BY employee_id) to count departments per employee. Then filter: keep rows where count = 1 (single dept) OR primary_flag = 'Y' (primary for multi-dept). This handles both cases in a single query without UNION or complex subqueries. TutorialsPoint - Primary Department for Each Employee | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Facebook 15
28.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen