Manager of the Largest Department - Practice Coding Problems

Manager of the Largest Department - Problem

Database Medium

You're working as a data analyst for a large corporation and need to identify the managers of the largest departments. Given an employee database, your task is to find which departments have the most employees and return the names of their managers.

You have access to an Employees table with the following structure:

Column Name	Type
emp_id	int
emp_name	varchar
dep_id	int
position	varchar

Key Requirements:

Find the department(s) with the maximum number of employees
Return the manager names from those departments
Handle cases where multiple departments are tied for largest
Sort results by dep_id in ascending order

Note: Each department has exactly one manager (employee with position = 'Manager').

Input & Output

example_1.sql — Basic Case

$ Input: Employees table: +--------+----------+--------+----------+ | emp_id | emp_name | dep_id | position | +--------+----------+--------+----------+ | 1 | Alice | 101 | Manager | | 2 | Bob | 101 | Engineer | | 3 | Carol | 101 | Engineer | | 4 | David | 102 | Manager | | 5 | Eve | 102 | Engineer | | 6 | Frank | 103 | Manager | +--------+----------+--------+----------+

› Output: +----------+ | emp_name | +----------+ | Alice | +----------+

💡 Note: Department 101 has 3 employees (Alice, Bob, Carol), which is the maximum. Alice is the manager of department 101.

example_2.sql — Tie Case

$ Input: Employees table: +--------+----------+--------+----------+ | emp_id | emp_name | dep_id | position | +--------+----------+--------+----------+ | 1 | Alice | 101 | Manager | | 2 | Bob | 101 | Engineer | | 3 | Carol | 102 | Manager | | 4 | David | 102 | Engineer | | 5 | Eve | 103 | Manager | +--------+----------+--------+----------+

💡 Note: Departments 101 and 102 both have 2 employees each (maximum). Both Alice (dept 101) and Carol (dept 102) are returned, sorted by department ID.

example_3.sql — Single Employee Departments

$ Input: Employees table: +--------+----------+--------+----------+ | emp_id | emp_name | dep_id | position | +--------+----------+--------+----------+ | 1 | Alice | 101 | Manager | | 2 | Bob | 102 | Manager | | 3 | Carol | 103 | Manager | +--------+----------+--------+----------+

› Output: +----------+ | emp_name | +----------+ | Alice | | Bob | | Carol | +----------+

💡 Note: All departments have exactly 1 employee each, so all managers are returned in order of department ID.

Constraints

1 ≤ number of employees ≤ 10⁴
1 ≤ emp_id ≤ 10⁶
1 ≤ dep_id ≤ 1000
emp_name consists of English letters and spaces
position is either 'Manager' or other job titles
Each department has exactly one manager

Visualization

Tap to expand

Understanding the Visualization

Count Employees

Walk through each department and count total employees

Find Maximum

Identify which department size is the largest

Get Managers

Find the managers of all departments with maximum size

Sort Results

Order the results by department ID

Key Takeaway

🎯 Key Insight: Use SQL aggregation (GROUP BY + COUNT) to find department sizes, then filter for departments with maximum size and return their managers. Window functions or CTEs make this efficient in a single query.

Asked in

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

The optimal solution uses Common Table Expressions (CTEs) or window functions to solve this in a single pass. First, count employees per department, then find the maximum count, and finally join to get managers from departments with maximum size. This approach is efficient with O(n) time complexity and handles ties naturally by returning all managers from equally largest departments.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n²)	O(n)	Count employees for each department separately with individual subqueries
Window Function Approach (Optimal)	O(n)	O(k)	Use window functions to calculate department sizes and filter in a single query

Brute Force (Multiple Subqueries) — Algorithm Steps

Find the maximum number of employees in any department
Find all departments with that maximum count
Get managers from those departments
Sort by department ID

Visualization

Tap to expand

Step-by-Step Walkthrough

First Pass

Scan all employees to count department sizes

Find Maximum

Identify the maximum department size

Second Pass

Find all departments with maximum size

Third Pass

Find managers of those departments

Code -

solution.c — C

// Brute Force SQL Solution
char* sqlQuery = 
"SELECT emp_name "
"FROM Employees "
"WHERE position = 'Manager' "
"  AND dep_id IN ( "
"    SELECT dep_id "
"    FROM ( "
"      SELECT dep_id, COUNT(*) as emp_count "
"      FROM Employees "
"      GROUP BY dep_id "
"    ) dept_counts "
"    WHERE emp_count = ( "
"      SELECT MAX(emp_count) "
"      FROM ( "
"        SELECT COUNT(*) as emp_count "
"        FROM Employees "
"        GROUP BY dep_id "
"      ) max_count "
"    ) "
"  ) "
"ORDER BY dep_id;";

// Simulation function
#include <stdlib.h>
#include <string.h>

typedef struct {
    int empId;
    char empName[100];
    int depId;
    char position[50];
} Employee;

typedef struct {
    int depId;
    int count;
} DeptCount;

char** bruteForceSolution(Employee* employees, int n, int* returnSize) {
    // Count employees per department
    DeptCount deptCounts[1000];
    int deptCountSize = 0;
    
    for (int i = 0; i < n; i++) {
        int found = 0;
        for (int j = 0; j < deptCountSize; j++) {
            if (deptCounts[j].depId == employees[i].depId) {
                deptCounts[j].count++;
                found = 1;
                break;
            }
        }
        if (!found) {
            deptCounts[deptCountSize].depId = employees[i].depId;
            deptCounts[deptCountSize].count = 1;
            deptCountSize++;
        }
    }
    
    // Find maximum count
    int maxCount = 0;
    for (int i = 0; i < deptCountSize; i++) {
        if (deptCounts[i].count > maxCount) {
            maxCount = deptCounts[i].count;
        }
    }
    
    // Find largest departments
    int largestDepts[1000];
    int largestDeptsSize = 0;
    for (int i = 0; i < deptCountSize; i++) {
        if (deptCounts[i].count == maxCount) {
            largestDepts[largestDeptsSize++] = deptCounts[i].depId;
        }
    }
    
    // Find managers
    char** result = malloc(n * sizeof(char*));
    *returnSize = 0;
    
    for (int i = 0; i < n; i++) {
        if (strcmp(employees[i].position, "Manager") == 0) {
            for (int j = 0; j < largestDeptsSize; j++) {
                if (employees[i].depId == largestDepts[j]) {
                    result[*returnSize] = malloc(100 * sizeof(char));
                    strcpy(result[*returnSize], employees[i].empName);
                    (*returnSize)++;
                    break;
                }
            }
        }
    }
    
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple full table scans - one to count employees per department, another to find max, and final scan to get managers

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary storage for intermediate results and department counts

⚡ Linearithmic Space

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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler