Primary Department for Each Employee - Practice Coding Problems

Primary Department for Each Employee - Problem

Database Easy

🏢 Primary Department for Each Employee

You work for a large corporation where employees can belong to multiple departments simultaneously. Each employee must designate exactly one department as their primary department for reporting purposes.

Given an Employee table that tracks department assignments, write a SQL query to find each employee's primary department. Here's the catch: when an employee belongs to only one department, that department is automatically their primary department, even though the flag might be 'N'.

📋 Table Structure: Employee

Column Name	Type
employee_id	int
department_id	int
primary_flag	varchar

Key Points:

🔑 Primary key: (employee_id, department_id)
📍 primary_flag: 'Y' = primary department, 'N' = not primary
⚠️ Special case: Employees with only one department have flag = 'N', but that department is still their primary

Goal: Return all employees with their primary department ID, handling both multi-department employees (flag='Y') and single-department employees (automatically primary).

Input & Output

example_1.sql — Basic Multi-Department Case

$ Input: Employee table: +-------------+---------------+--------------+ | employee_id | department_id | primary_flag | +-------------+---------------+--------------+ | 1 | 1 | N | | 1 | 2 | Y | | 2 | 3 | N | | 3 | 3 | N | | 3 | 4 | Y | +-------------+---------------+--------------+

› Output: +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 1 | 2 | | 2 | 3 | | 3 | 4 | +-------------+---------------+

💡 Note: Employee 1 has 2 departments, primary is department 2 (flag='Y'). Employee 2 has only 1 department, so department 3 is primary by default. Employee 3 has 2 departments, primary is department 4 (flag='Y').

example_2.sql — Single Department Employees

$ Input: Employee table: +-------------+---------------+--------------+ | employee_id | department_id | primary_flag | +-------------+---------------+--------------+ | 10 | 100 | N | | 20 | 200 | N | | 30 | 300 | N | +-------------+---------------+--------------+

› Output: +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 10 | 100 | | 20 | 200 | | 30 | 300 | +-------------+---------------+

💡 Note: All employees have only one department each, so their single departments are automatically their primary departments, even though primary_flag is 'N'.

example_3.sql — Mixed Case Scenario

$ Input: Employee table: +-------------+---------------+--------------+ | employee_id | department_id | primary_flag | +-------------+---------------+--------------+ | 1 | 10 | Y | | 1 | 20 | N | | 1 | 30 | N | | 2 | 40 | N | | 3 | 50 | N | | 3 | 60 | Y | +-------------+---------------+--------------+

› Output: +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 1 | 10 | | 2 | 40 | | 3 | 60 | +-------------+---------------+

💡 Note: Employee 1 has 3 departments with primary=10. Employee 2 has 1 department, so 40 is primary by default. Employee 3 has 2 departments with primary=60.

Constraints

1 ≤ employee_id ≤ 10⁴
1 ≤ department_id ≤ 10⁴
primary_flag ∈ {'Y', 'N'}
Each employee has at least one department
If an employee has multiple departments, exactly one has primary_flag = 'Y'

Visualization

Tap to expand

Understanding the Visualization

Group by Employee

Organize all department assignments by employee ID

Count Departments

For each employee, count how many departments they belong to

Apply Logic

If count=1, that's the primary. If count>1, find where primary_flag='Y'

Extract Results

Return the employee-primary department pairs

Key Takeaway

🎯 Key Insight: Window functions enable elegant single-pass solutions by allowing aggregation (counting departments) alongside row-level data access in the same query, eliminating the need for multiple table scans or complex subqueries.

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 28

The optimal solution uses a window function approach with conditional logic to handle both single and multi-department employees in a single query. Key insight: use COUNT(*) OVER (PARTITION BY employee_id) to count departments per employee, then apply conditional filtering where (dept_count = 1) OR (dept_count > 1 AND primary_flag = 'Y'). This achieves O(n) time complexity with a single table scan.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function Approach (Optimal)	O(n)	O(n)	Use window functions to count departments and identify primary in one pass
Brute Force (Multiple Queries)	O(n²)	O(n)	Separate queries for single-department and multi-department employees

Window Function Approach (Optimal) — Algorithm Steps

Use COUNT() window function to count departments per employee
Apply CASE logic: if count=1, select that department; else select where primary_flag='Y'
Filter results to only include primary departments
Return employee_id and department_id pairs

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition

Window function partitions data by employee_id

Count

COUNT(*) calculates departments per employee in each partition

Filter

Apply conditional logic to select primary departments

Code -

solution.c — C

// SQL Query - Optimal Window Function Approach
#include <stdio.h>

const char* optimal_solution() {
    return ""
    "SELECT employee_id, department_id\n"
    "FROM (\n"
    "    SELECT \n"
    "        employee_id,\n"
    "        department_id,\n"
    "        primary_flag,\n"
    "        COUNT(*) OVER (PARTITION BY employee_id) as dept_count\n"
    "    FROM Employee\n"
    ") ranked\n"
    "WHERE \n"
    "    (dept_count = 1) OR \n"
    "    (dept_count > 1 AND primary_flag = 'Y')\n";
}

const char* alternative_solution() {
    return ""
    "SELECT employee_id, department_id\n"
    "FROM Employee\n"
    "WHERE primary_flag = 'Y'\n"
    "   OR employee_id IN (\n"
    "       SELECT employee_id\n"
    "       FROM Employee\n"
    "       GROUP BY employee_id\n"
    "       HAVING COUNT(*) = 1\n"
    "   )\n";
}

int main() {
    printf("%s", optimal_solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table with window function aggregation

✓ Linear Growth

Space Complexity

O(n)

Window function requires memory for partitioning by employee_id

⚡ Linearithmic Space

58.2K Views

High Frequency

~15 min Avg. Time

1.8K 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

🏢 Primary Department for Each Employee

📋 Table Structure: Employee

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Function Approach (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler