Welcome to Subscribe On Youtube
1076. Project Employees II
Description
Table: Project
+-------------+---------+ | Column Name | Type | +-------------+---------+ | project_id | int | | employee_id | int | +-------------+---------+ (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 Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id is the primary key (column with unique values) of this table. Each row of this table contains information about one employee.
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_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 | +-------------+--------+------------------+ Output: +-------------+ | project_id | +-------------+ | 1 | +-------------+ Explanation: The first project has 3 employees while the second one has 2.
Solutions
-
# 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 ); -- SELECT project_id FROM Project GROUP BY 1 HAVING COUNT(1) >= ALL ( SELECT COUNT(1) FROM Project GROUP BY project_id );