project-employees-ii.sql

-- 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.

# V0
WITH prod_count AS (
SELECT
project_id,
COUNT(1) AS count_
FROM
Project
GROUP BY project_id
ORDER BY count_ DESC
LIMIT 1
)
SELECT project_id FROM prod_count

# V1
# https://code.dennyzhang.com/project-employees-ii
select project_id
from Project
group by project_id
having count(1) = (
    select count(1)
    from Project
    group by project_id
    order by count(1) desc
    limit 1)
    
# V2 
# Time:  O(n)
# Space: O(n)
SELECT project_id 
FROM   project 
GROUP  BY project_id 
HAVING Count(employee_id) = (SELECT Count(employee_id) 
                             FROM   project 
                             GROUP  BY project_id 
                             ORDER  BY Count(employee_id) DESC 
                             LIMIT  1) 
ORDER  BY NULL