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 EmployeeInput: Employee Tableemployee_iddepartment_idprimary_flag11N21Y22N33NSQL LogicGROUP BY employee_idIF COUNT(*) = 1: Return single deptELSE: Return Y-flagged deptOutputemployee_iddepartment_id112133
Understanding the Visualization
1
Input
Employee-department relationships
2
Group & Analyze
Group by employee, apply logic
3
Output
Primary department per employee
Key Takeaway
🎯 Key Insight: Use conditional aggregation to handle different employee scenarios in a single query

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