Find Every Project with the Most Employees
Company: Otter.Ai
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
The interview report explicitly identified the “Project with Most Employees” SQL exercise. Use the following self-contained PostgreSQL schema:
```text
employees
---------
employee_id BIGINT PRIMARY KEY
name TEXT NOT NULL
experience_years INTEGER NOT NULL
project_assignments
-------------------
project_id BIGINT NOT NULL
employee_id BIGINT NOT NULL REFERENCES employees(employee_id)
PRIMARY KEY (project_id, employee_id)
```
Write one PostgreSQL query that returns the project or projects with the greatest number of assigned employees.
Return exactly one column:
- `project_id`
Return all tied projects. Because the assignment table's primary key prevents duplicate employee assignments within a project, each row counts as one assigned employee. If there are no assignments, return no rows. Order the result by `project_id` ascending.
Quick Answer: Build a PostgreSQL aggregation that returns every project tied for the largest employee count. This compact exercise checks grouping, maximum selection, tie preservation, empty-input behavior, and careful use of primary-key guarantees.