Projects With Maximum Team Size

Easy

You are given two tables from a project staffing system. Project table: project_id: identifier of a project employee_id: identifier of an assigned employee (project_id, employee_id) is unique Employee table: employee_id: unique employee identifier name: employee name experience_years: years of experience Task: Compute team size for every project as the number of assigned employees. Return all projects whose team size equals the maximum team size across the dataset. If multiple projects tie at the maximum, include all of them. Output requirements: Return exactly one column named project_id. Row order may be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Project:
| project_id | employee_id |
|------------|-------------|
| 1          | 1           |
| 1          | 2           |
| 1          | 3           |
| 2          | 1           |
| 2          | 4           |

Employee:
| employee_id | name   | experience_years |
|-------------|--------|------------------|
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 1                |
| 4           | Doe    | 2                |

Output

[
  {"project_id":1}
]

Explanation

Project 1 has three assigned employees, while Project 2 has two.

Example 2

Input

Project:
| project_id | employee_id |
|------------|-------------|
| 10         | 101         |
| 10         | 102         |
| 20         | 101         |
| 20         | 103         |
| 30         | 102         |

Employee:
| employee_id | name  | experience_years |
|-------------|-------|------------------|
| 101         | Nia   | 5                |
| 102         | Omar  | 6                |
| 103         | Leah  | 4                |

Output

[
  {"project_id":10},
  {"project_id":20}
]

Explanation

Projects 10 and 20 both have team size 2, which is the maximum.

Example 3

Input

Project:
| project_id | employee_id |
|------------|-------------|
| 7          | 1           |
| 7          | 2           |
| 8          | 1           |
| 8          | 3           |
| 9          | 2           |
| 9          | 3           |

Employee:
| employee_id | name  | experience_years |
|-------------|-------|------------------|
| 1           | Asha  | 8                |
| 2           | Ben   | 2                |
| 3           | Cara  | 7                |

Output

[
  {"project_id":7},
  {"project_id":8},
  {"project_id":9}
]

Explanation

All three projects tie with the same team size, so all are returned.

Constraints

1 <= number of rows in Project <= 200,000
1 <= number of rows in Employee <= 200,000
1 <= project_id <= 1,000,000,000
1 <= employee_id <= 1,000,000,000
(project_id, employee_id) is unique in Project
Every Project.employee_id references an existing employee row
At least one project appears in Project
Return exactly one column named project_id
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with one column: project_id

Project: | project_id | employee_id | |------------|-------------| | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | Employee: | employee_id | name | experience_years | |-------------|--------|------------------| | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 1 | | 4 | Doe | 2 |

Project: | project_id | employee_id | |------------|-------------| | 10 | 101 | | 10 | 102 | | 20 | 101 | | 20 | 103 | | 30 | 102 | Employee: | employee_id | name | experience_years | |-------------|-------|------------------| | 101 | Nia | 5 | | 102 | Omar | 6 | | 103 | Leah | 4 |

Project: | project_id | employee_id | |------------|-------------| | 7 | 1 | | 7 | 2 | | 8 | 1 | | 8 | 3 | | 9 | 2 | | 9 | 3 | Employee: | employee_id | name | experience_years | |-------------|-------|------------------| | 1 | Asha | 8 | | 2 | Ben | 2 | | 3 | Cara | 7 |