Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1076.html
1076. Project Employees II
Level
Easy
Description
Table: Project
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.
Table: Employee
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
employee_id is the primary key of this table.
Write an SQL query that reports all the projects that have the most employees.
The query result format is in the following example:
Project table:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
Result table:
+-------------+
| project_id |
+-------------+
| 1 |
+-------------+
The first project has 3 employees while the second one has 2.
Solution
Find the maximum occurrence of project_id in table Project. Then select from table Project the project_ids with the maximum occurrence.
# Write your MySQL query statement below
select project_id
from Project
group by project_id
having count(distinct employee_id) = (
select max(count_employee_id) from (
select project_id, count(employee_id) as count_employee_id
from Project
group by project_id
) as max_employee
);
-- same as above, but separated into 2 parts
WITH max_employee AS (
SELECT project_id, COUNT(employee_id) AS count_employee_id
FROM Project
GROUP BY project_id
)
SELECT project_id
FROM Project
GROUP BY project_id
HAVING COUNT(DISTINCT employee_id) = (
SELECT MAX(count_employee_id)
FROM max_employee
);