Department Leaders in Maximum-Size Teams

Medium

A workforce intelligence platform stores one row per employee in a company directory. Table: Employees emp_id: unique employee identifier emp_name: employee display name dep_id: department identifier position: job title label Task: Identify every department with the highest employee count, then return the manager for each of those departments. Business rules: Department size is the number of rows in Employees for that dep_id. A manager row is identified by position = "Manager". Multiple departments can tie for largest size; return all of them. Output requirements: Return columns exactly in this order: manager_name dep_id Sort by dep_id in ascending order. Supported submission formats: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Employees:
| emp_id | emp_name | dep_id | position      |
|--------|----------|--------|---------------|
| 11     | Kai      | 200    | Manager       |
| 12     | Nora     | 200    | Engineer      |
| 13     | Omar     | 200    | Analyst       |
| 14     | Pia      | 200    | Consultant    |
| 21     | Ari      | 300    | Manager       |
| 22     | Rhea     | 300    | Engineer      |
| 23     | Sol      | 300    | Designer      |
| 24     | Tia      | 300    | Coordinator   |
| 31     | Uma      | 150    | Manager       |
| 32     | Vee      | 150    | Engineer      |

Output

[
  {"manager_name":"Kai","dep_id":200},
  {"manager_name":"Ari","dep_id":300}
]

Explanation

Departments 200 and 300 both have four employees, which is the global maximum. Their managers are returned.

Example 2

Input

Employees:
| emp_id | emp_name | dep_id | position      |
|--------|----------|--------|---------------|
| 41     | Lena     | 10     | Manager       |
| 42     | Miro     | 10     | Engineer      |
| 43     | Niko     | 10     | Analyst       |
| 51     | Opal     | 20     | Manager       |
| 52     | Quin     | 20     | Engineer      |

Output

[
  {"manager_name":"Lena","dep_id":10}
]

Explanation

Department 10 has three employees while department 20 has two, so only manager Lena is returned.

Example 3

Input

Employees:
| emp_id | emp_name | dep_id | position      |
|--------|----------|--------|---------------|
| 61     | Sam      | 501    | Manager       |
| 62     | Teo      | 501    | Engineer      |
| 71     | Sam      | 777    | Manager       |
| 72     | Uma      | 777    | Engineer      |
| 81     | Wes      | 999    | Manager       |
| 82     | Xia      | 999    | Engineer      |

Output

[
  {"manager_name":"Sam","dep_id":501},
  {"manager_name":"Sam","dep_id":777},
  {"manager_name":"Wes","dep_id":999}
]

Explanation

All departments have the same size. Each manager is returned, even when manager names are duplicated across departments.

Constraints

1 <= number of rows in Employees <= 200000
1 <= emp_id <= 1000000000
1 <= dep_id <= 1000000000
emp_id is unique
Each department has exactly one row with position = 'Manager'
emp_name and position are non-empty strings
Department size includes every employee row in that department
Return exactly columns: manager_name, dep_id
Sort output by dep_id ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact required columns

Employees: | emp_id | emp_name | dep_id | position | |--------|----------|--------|---------------| | 11 | Kai | 200 | Manager | | 12 | Nora | 200 | Engineer | | 13 | Omar | 200 | Analyst | | 14 | Pia | 200 | Consultant | | 21 | Ari | 300 | Manager | | 22 | Rhea | 300 | Engineer | | 23 | Sol | 300 | Designer | | 24 | Tia | 300 | Coordinator | | 31 | Uma | 150 | Manager | | 32 | Vee | 150 | Engineer |

Employees: | emp_id | emp_name | dep_id | position | |--------|----------|--------|---------------| | 41 | Lena | 10 | Manager | | 42 | Miro | 10 | Engineer | | 43 | Niko | 10 | Analyst | | 51 | Opal | 20 | Manager | | 52 | Quin | 20 | Engineer |

Employees: | emp_id | emp_name | dep_id | position | |--------|----------|--------|---------------| | 61 | Sam | 501 | Manager | | 62 | Teo | 501 | Engineer | | 71 | Sam | 777 | Manager | | 72 | Uma | 777 | Engineer | | 81 | Wes | 999 | Manager | | 82 | Xia | 999 | Engineer |