Manager of the Largest Department - Problem

You are given an Employees table containing employee information including their department ID and position.

Task: Find the name of the manager from the largest department (department with the most employees).

Important notes:

  • There may be multiple largest departments when the number of employees is the same
  • Return results sorted by dep_id in ascending order
  • Only return managers from departments that have the maximum employee count

Table Schema

Employees
Column Name Type Description
emp_id PK int Unique employee identifier
emp_name varchar Employee name
dep_id int Department identifier
position varchar Employee position (Manager, Developer, etc.)
Primary Key: emp_id
Note: Each row represents one employee with their department and position

Input & Output

Example 1 — Multiple departments with different sizes
Input Table:
emp_id emp_name dep_id position
1 Alice 1 Manager
2 Bob 1 Developer
3 Charlie 2 Manager
4 David 3 Manager
5 Eve 3 Developer
Output:
emp_name
Alice
David
💡 Note:

Department 1 has 2 employees (Alice, Bob), Department 2 has 1 employee (Charlie), Department 3 has 2 employees (David, Eve). The largest departments are 1 and 3 with 2 employees each. The managers of these departments are Alice and David, returned sorted by dep_id.
Example 2 — Single largest department
Input Table:
emp_id emp_name dep_id position
1 John 1 Manager
2 Jane 1 Developer
3 Mike 1 Analyst
4 Sarah 2 Manager
Output:
emp_name
John
💡 Note:

Department 1 has 3 employees (John, Jane, Mike) and Department 2 has 1 employee (Sarah). Department 1 is the largest with 3 employees, so we return its manager John.
Example 3 — All departments same size
Input Table:
emp_id emp_name dep_id position
1 Tom 1 Manager
2 Lisa 2 Manager
3 Rick 3 Manager
Output:
emp_name
Tom
Lisa
Rick
💡 Note:

All departments have exactly 1 employee each (all managers). Since all departments are tied for largest size, we return all managers sorted by dep_id: Tom, Lisa, Rick.

Constraints

  • 1 ≤ emp_id ≤ 100
  • 1 ≤ dep_id ≤ 100
  • emp_name consists of English letters and spaces
  • position is a valid job title including 'Manager'

Visualization

Tap to expand
Manager of the Largest Department INPUT TABLES Employee Table emp_id emp_name dep_id 1 Alice 1 2 Bob 1 3 Charlie 2 4 Diana 2 5 Eve 3 Department Table dep_id dep_name manager 1 Sales John 2 Marketing Mary 3 IT Tom ALGORITHM STEPS 1 Count Employees GROUP BY dep_id, COUNT(*) as emp_count 2 Find Max Count MAX(emp_count) to find largest department size 3 Filter Largest Depts WHERE count = max_count (handles ties) 4 Join and Sort JOIN with Department, ORDER BY dep_id ASC Counts: dep_id 1: 2 employees dep_id 2: 2 employees dep_id 3: 1 employee MAX = 2 (ties: 1,2) FINAL RESULT Largest Departments Found Dept 1: Sales 2 employees Manager: John Dept 2: Marketing 2 employees Manager: Mary Output Table dep_id manager 1 John 2 Mary OK - Sorted by dep_id ASC Key Insight: Use a subquery with GROUP BY to count employees per department, then find the maximum count. Filter departments with this max count (handles ties), JOIN with Department table to get manager names, and ORDER BY dep_id ASC for consistent output. Window functions like RANK() can also solve this elegantly. TutorialsPoint - Manager of the Largest Department | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
28.5K Views
Medium Frequency
~12 min Avg. Time
890 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