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