Primary Department for Each Employee | Leetcode 1789

1. Why Do We Need This? (Problem Statement & Status Quo)

In relational database management systems, real-world entities often exhibit one-to-many or many-to-many relationships. Consider an enterprise HR system where a single employee can belong to multiple departments simultaneously (e.g., an engineer working in both the Core Platform team and the Infrastructure team).

When querying such a database, data analysts and engineers frequently face a specific business logic challenge:

• Employees belonging to only one department must have that sole department designated as their primary department, regardless of internal flag values.
• Employees belonging to multiple departments have an explicit designation flag ('y' or 'n') indicating which department is their primary operational unit.

The core objective is to write an optimal SQL query that extracts each employee along with their correct primary department, correctly handling both single-department employees and multi-department employees.

2. Core Mental Model & Scenario Breakdown

To understand how employees relate to departments, let's examine the structure of the input table (Employee) provided in Leetcode 1789.

Table Schema: Employee

Composite Primary Key

The table constraint specifies that both employee_id and department_id combined form the composite primary key:

This means an individual employee_id can appear multiple times (once for each department they belong to), but the exact combination of employee_id and department_id is unique.

Real-Life Analogy: The Corporate Dual-Role Employee

Imagine a large corporation where employees wear multiple hats.

• Scenario A (The Specialist): Employee #1 works exclusively in Department 1. They don't have a choice or a "secondary" badge—their only department defaults to their primary department, even if marked with 'n'.
• Scenario B (The Cross-Functional Lead): Employee #2 works in both Department 1 and Department 2. Management issues a special primary badge ('y') for Department 1 to indicate where their primary administrative responsibilities lie, while Department 2 holds an 'n' flag.

3. Step-by-Step Evolution of Business Rules

Let's analyze the expected output for various employee profiles to crystallize our filtering logic:

From this flow, we derive two distinct conditions for a department to be considered "primary":

1. Explicit Primary Condition: The row has primary_flag = 'y'.
2. Implicit Primary Condition: The employee belongs to exactly one department in total (COUNT(*) = 1), regardless of whether the flag is 'y' or 'n'.

4. Approach Comparison: Choosing the Strategy

There are multiple ways to solve this problem in SQL. Below is a comparative synthesis of standard approaches:

5. Writing the SQL Query (Subquery Method)

We will implement the solution using the Subquery Method, combining our two business rules with an OR operator inside the WHERE clause.

SELECT DISTINCT employee_id, department_id
FROM Employee
WHERE primary_flag = 'y'
   OR employee_id IN (
       SELECT employee_id
       FROM Employee
       GROUP BY employee_id
       HAVING COUNT(department_id) = 1
   )
ORDER BY employee_id;

6. Deep Dive into Query Execution & Clauses

A. Why DISTINCT?

SELECT DISTINCT employee_id, department_id

Even though the composite key prevents duplicate rows of the exact same (employee_id, department_id) pair, using DISTINCT ensures safety against potential duplicates introduced by certain join or union patterns, and guarantees clean, distinct result sets.

B. The WHERE Clause Logic

WHERE primary_flag = 'y'
   OR employee_id IN (...)

The WHERE clause evaluates two independent conditions separated by OR:

• Condition 1 (primary_flag = 'y'): Instantly captures all rows designated as primary for multi-department employees.
• Condition 2 (employee_id IN (...)): Captures single-department employees whose rows might carry an 'n' flag.

C. The Subquery (GROUP BY vs. HAVING)

SELECT employee_id
FROM Employee
GROUP BY employee_id
HAVING COUNT(department_id) = 1

• Why GROUP BY employee_id? We need to aggregate rows per employee to evaluate their total department footprint.
• Why HAVING instead of WHERE?

  Note: The WHERE clause filters rows before aggregation takes place, meaning aggregate functions like COUNT() cannot be used inside WHERE. The HAVING clause executes after the GROUP BY phase, allowing us to filter groups based on aggregate results (in this case, ensuring COUNT(department_id) = 1).

7. Complete Flow Walkthrough

Let's trace how the database engine executes our query step by step:

8. Summary and Key Takeaways

• Understand Composite Keys: Recognize that employee_id repeats across rows while the combination of employee_id and department_id is unique.
• Dual Business Rules: Always account for both explicit flags (primary_flag = 'y') and implicit single-item exceptions (COUNT(*) = 1).
• WHERE vs HAVING Execution Order: Remember that WHERE filters raw table rows prior to grouping, whereas HAVING filters aggregated groups (making it mandatory for COUNT() checks).
• Multiple Solution Paths: This problem can be solved via Subqueries, UNION operations, or Window Functions (ROW_NUMBER()). Choose the one that maximizes readability and maintainability for your team.
• Data Hygiene: Always include ORDER BY clauses when output ordering is expected or test-verified, even if problem descriptions state that "order does not matter."