Find Every Project with the Most Employees
Find Every Project with the Most Employees
Given the following 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 a single PostgreSQL query that returns the project_id for the project(s) with the highest number of assigned employees. The result should be a single column named project_id.
If multiple projects are tied for the most employees, return all of them. If there are no project assignments, return an empty result set. The final result should be ordered by project_id in ascending order.
AI interview prep
Core dilemma
Efficiently aggregating counts across a many-to-many relationship and identifying the maximum value while handling ties and edge cases (no data).
2 tradeoffs and 2 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Find Every Project with the Most Employees
Find Every Project with the Most Employees
Given the following 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 a single PostgreSQL query that returns the project_id for the project(s) with the highest number of assigned employees. The result should be a single column named project_id.
If multiple projects are tied for the most employees, return all of them. If there are no project assignments, return an empty result set. The final result should be ordered by project_id in ascending order.