Description

Table: Project

Column NameType
project_idint
employee_idint
  • (project_id, employee_id) is the primary key (combination of columns with unique values) of this table.
  • employee_id is a foreign key (reference column) to Employee table.
  • Each row of this table indicates that the employee with employee_id is working on the project with project_id.

Table: Employee

Column NameType
employee_idint
namevarchar
experience_yearsint
  • employee_id is the primary key (column with unique values) of this table.
  • Each row of this table contains information about one employee.

Problem Statement

Write a solution to report all the projects that have the most employees.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:

  • Project table:
project_idemployee_id
11
12
13
21
24
  • Employee table:
employee_idnameexperience_years
1Khaled3
2Ali2
3John1
4Doe2

Output:

project_id
1

Explanation:

  • The first project has 3 employees while the second one has 2.

Solution

This problem is again a problem that requires aggregation. In this case, you need to find the number of employees per project. This you can do using information from only Project table, so there is no need to join this table with Employee table.

1SELECT project_id
2    FROM Project
3    GROUP BY project_id
4    ORDER BY COUNT(employee_id) DESC
5    LIMIT 1;

This solution will work as long as there is only one project_id which has the most employees. But, what if there are multiple projects with the same number of employees? In that case, you need to find the project_id which has the maximum number of employees and you might need to output multiple project_ids.

To achieve this, you first need to find the maximum number of employees.

1SELECT COUNT(employee_id) AS max_employees
2    FROM Project
3    GROUP BY project_id
4    ORDER BY max_employees DESC
5    LIMIT 1;

Next, you need to fidn the project_id which has the same number of employees as the maximum number of employees.

The final solution looks like this.

 1SELECT project_id
 2    FROM Project
 3    GROUP BY project_id
 4    HAVING COUNT(employee_id) = (
 5        SELECT COUNT(employee_id) AS max_employees
 6            FROM Project
 7            GROUP BY project_id
 8            ORDER BY max_employees DESC
 9            LIMIT 1
10    );