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
);

All Problems

All Solutions