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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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