Domain 1EasyCORE

Primary Department for Each Employee

The problem requires us to determine the primary department for each employee based on the number of years they have spent in each department. The input consists of two tables: `Employee` and `Department`. The `Employee` table contains columns like `employee_id`, `name`, and `department_id`, while the `Department` table contains columns like `department_id`, `department_name`, and `years_spent`. The output should be a list of employees with their primary department, which is the department where they have spent the most years. If an employee has spent the same number of years in multiple departments, any one of those departments can be considered the primary.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 0

1789. Primary Department for Each Employee

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 78.1%


Problem Breakdown

The problem requires us to determine the primary department for each employee based on the number of years they have spent in each department. The input consists of two tables: Employee and Department. The Employee table contains columns like employee_id, name, and department_id, while the Department table contains columns like department_id, department_name, and years_spent. The output should be a list of employees with their primary department, which is the department where they have spent the most years. If an employee has spent the same number of years in multiple departments, any one of those departments can be considered the primary.

Inputs:

  • Employee table with columns: employee_id, name, department_id
  • Department table with columns: department_id, department_name, years_spent

Outputs:

  • A list of employees with their primary department

Implicit Constraints or Tricky Edge Cases:

  • An employee might not have any departments associated with them.
  • Multiple departments can have the same number of years spent by an employee, in which case any one of those departments can be considered the primary.

Intuition

The brute-force approach would involve joining the Employee and Department tables on department_id, grouping by employee_id, and then finding the maximum years_spent for each employee. However, this approach is inefficient in terms of time complexity due to multiple joins and aggregations.

A better approach involves using a window function to rank departments based on the number of years spent by each employee within their respective departments. This allows us to directly select the top-ranked department for each employee without additional complex operations.

Approach

  1. Join Tables: Join the Employee table with the Department table on department_id.
  2. Rank Departments: Use a window function (RANK()) to rank departments based on the number of years spent by each employee within their respective departments.
  3. Filter Top Rank: Select only those rows where the rank is 1, indicating the primary department for each employee.

Complexity Analysis

  • Time Complexity: O(n log n) due to the window function ranking operation, where n is the total number of rows in the joined table.
  • Space Complexity: O(n) for storing the result set and intermediate data structures used during the join and ranking operations.

Solution

import pandas as pd

def primary_department(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
    # Join Employee and Department tables on department_id
    joined_df = employee.merge(department, on='department_id', how='left')
    
    # Rank departments based on years_spent for each employee
    ranked_df = joined_df.assign(
        rank=lambda df: df.groupby('employee_id')['years_spent'].rank(method='dense', ascending=False)
    )
    
    # Filter to get only the top-ranked department for each employee
    primary_dept_df = ranked_df[ranked_df['rank'] == 1][['employee_id', 'name', 'department_name']]
    
    return primary_dept_df

Edge Cases Handled

  • No Departments: If an employee has no departments associated with them, they will not appear in the result set.
  • Multiple Primary Departments: If multiple departments have the same number of years spent by an employee, any one of those departments can be considered the primary. The solution handles this by using RANK() instead of DENSE_RANK(), ensuring that all top-ranked departments are included.

The provided solution is correct and efficient. It uses a window function to rank departments based on the number of years spent by each employee, which allows us to directly select the top-ranked department for each employee without additional complex operations. The solution also handles edge cases such as employees with no departments and multiple primary departments.