Find Every Project with the Most Employees

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.

Find Every Project with the Most Employees

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.

Table employees

column	type	constraints
employee_id	BIGINT	PRIMARY KEY
name	TEXT	NOT NULL
experience_years	INTEGER	NOT NULL

Sample data:

employee_id	experience_years	name
1	8	Ada
2	4	Bo
3	6	Cora
4	2	Dev
5	10	Emi
6	3	Finn
7	7	Gia
8	5	Hugo
9	9	Inez
10	1	Jae
11	12	Kai

Table project_assignments

column	type	constraints
project_id	BIGINT	NOT NULL
employee_id	BIGINT	NOT NULL, REFERENCES employees(employee_id)

Sample data:

employee_id	project_id
1	101
2	101
3	101
4	101
2	205
5	205
6	205
7	205
1	309
8	309
9	309
10	412

Expected output

project_id
101
205

Hints

1. First count assignment rows for each project.
2. Rank the per-project counts so every project tied for the largest count is retained.