SQL Interview Questions – Project Employees II

Problem Description –

Write an SQL query that reports all the projects that have the most employees.

Return the result table in any order.

The query result format is in the following example.

Difficulty Level – Easy

Problem Link – https://leetcode.com/problems/project-employees-ii/

Solution –

SELECT 
    project_id
FROM (
    SELECT 
        project_id,
        DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) as rnk
    FROM project
    GROUP BY 1
    ) x
WHERE rnk = 1

This is a common interview question where they ask you to find something in this case project id which has highest number of employees or product which has the highest number of sales. You can solve this problem with COUNT and LIMIT like this –

SELECT
	project_id
FROM (
	SELECT 
		project_id,
		COUNT(*) as total_employees
	FROM project
	GROUP BY 1
	) x 
ORDER BY total_employees DESC
LIMIT 1

But there is one problem with this approach. If there is a tie and more than one project has highest number of employees then you won’t get the right result as it is not obvious beforehand how many rows to limit.

That’s why we use the DENSE_RANK to solve the problem. It will resolve the above problem if there is a tie and more than one projects has most numbers of employees.