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.
Overview: 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.
Use PostgreSQL with the `employees` and `project_assignments` tables shown below. Write one query that returns the project or projects with the greatest number of assigned employees. Return exactly one column named `project_id`, return all ties, and order the result by `project_id` ascending. The `(project_id, employee_id)` pair is unique, so every assignment row counts as one assigned employee. If `project_assignments` is empty, return no rows.
Tables
employees(employee_id BIGINT, name TEXT, experience_years INTEGER)
project_assignments(project_id BIGINT, employee_id BIGINT)
Hints
- First count assignment rows for each project.
- Rank the per-project counts so every project tied for the largest count is retained.