Katkat
Back

Find Every Project with the Most Employees

Otter.AiData Engineer · Technical Screen
MediumData EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

The interview report explicitly identified the “Project with Most Employees” SQL exercise. Use the following self-contained PostgreSQL schema:

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.