SQL interview question: Department With the Most Employees

Departments With the Most Recent Hires

HR wants to know which part of the company has grown the most in recent years, and two teams may well be level.

Find the department or departments that hired the most employees on or after 2021-01-01. Return department (the department name) and hires (how many people that department hired in that period). If several departments tie for the top, return all of them, ordered by department.

Skills: GROUP BY with COUNT, comparing against MAX in a CTE, and keeping ties

Tables

employees 37 rows
  • id integer PK
  • first_name text
  • last_name text
  • email text
  • department_id integer → departments
  • salary integer
  • hire_date text
  • manager_id integer → employees
  • title text
idfirst_namelast_nameemaildepartment_idsalaryhire_datemanager_idtitle
1AliceChen[email protected]12500002015-01-15NULLCEO
2RobertMartinez[email protected]21800002016-03-20NULLCEO
3SarahJohnson[email protected]11500002017-06-101VP Engineering
departments 5 rows
  • id integer PK
  • name text
  • location text
idnamelocation
1EngineeringSan Francisco
2SalesNew York
3MarketingLos Angeles
About the employees sample database

How to solve it

To find the department with the most employees, count employees per department and keep the count that equals the maximum: GROUP BY department_id HAVING COUNT(*) = (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM employees GROUP BY department_id)). Avoid ORDER BY COUNT(*) DESC LIMIT 1 unless you're sure there can't be a tie, because it returns only one department.

Count first, then pick the top

Every version of this question has two steps. First you collapse employees into one row per department with GROUP BY and COUNT(*). Then you keep the department whose count is the largest. The second step is where answers go wrong.

On the sample employees table, the first step looks like this:

SELECT d.name AS department, COUNT(*) AS employees
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.id, d.name
ORDER BY employees DESC;

Engineering has 13 people, Sales 9, Marketing 6, Finance 5 and HR 4. That's all 37 employees, since nobody has a NULL department.

Why LIMIT 1 is a trap

The quickest answer is to sort and take the first row:

SELECT d.name AS department, COUNT(*) AS employees
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.id, d.name
ORDER BY employees DESC
LIMIT 1;

It returns Engineering with 13, and for full headcount that's right. Now ask the same thing about people hired since 2021. Marketing and Sales both hired 3, and the other three departments hired 2 each. The same query with WHERE e.hire_date >= '2021-01-01' returns just one row (Sales when we ran it) and silently drops Marketing. Which of the tied rows survives isn't defined, so it can change between databases or runs.

That's why the practice question above uses recent hires. The real data has a tie, and LIMIT 1 gets it wrong.

Approach 1: compare with MAX in a subquery

The portable fix is to compute the top count separately and keep every group that matches it:

SELECT d.name AS department, COUNT(*) AS employees
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.id, d.name
HAVING COUNT(*) = (
  SELECT MAX(cnt)
  FROM (SELECT COUNT(*) AS cnt FROM employees GROUP BY department_id)
);

The inner query counts per department, the middle one takes the largest count (13), and HAVING keeps the groups equal to it. It works in every major dialect, though some need an alias on the derived table. The solution above does the same thing with a CTE, which saves writing the count twice.

You might try HAVING COUNT(*) = MAX(COUNT(*)) to skip the subquery. SQLite rejects it with "misuse of aggregate function COUNT()", and so do most databases, because an aggregate can't wrap another aggregate in the same query level.

Approach 2: RANK over the counts

A window function can rank the grouped rows directly, because window functions run after GROUP BY:

WITH ranked AS (
  SELECT d.name AS department, COUNT(*) AS hires,
         RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM employees e
  JOIN departments d ON d.id = e.department_id
  WHERE e.hire_date >= '2021-01-01'
  GROUP BY d.id, d.name
)
SELECT department, hires
FROM ranked
WHERE rnk = 1
ORDER BY department;

This returns Marketing and Sales, 3 each. Use RANK or DENSE_RANK here, not ROW_NUMBER, which numbers tied rows 1 and 2 and brings back the LIMIT 1 problem. The RANK vs DENSE_RANK page shows how the three differ. The ranking version also extends nicely when the follow-up is "the top two departments".

Group by the right thing

Group by the department id, not by something that only looks like it. In this data two departments, Sales and Finance, are both in New York. Group by location and New York comes out on top with 14 people, which beats Engineering's 13 and answers a different question.

If the interviewer also wants departments with nobody in them, start from departments and LEFT JOIN employees, counting e.id instead of *. Every department here has employees, so the result doesn't change here, but mention it anyway.

What to say in the interview

Write the GROUP BY first and say the counts out loud. Then ask whether ties should be returned, and use the MAX subquery or RANK rather than LIMIT 1. If they only want a threshold instead of the top, that's a plain HAVING filter, as in Departments With More Than 5 Employees. The GROUP BY questions cover more counting patterns.

Common questions

How do I find the department with the maximum number of employees in SQL?
Count employees with GROUP BY department_id, then keep the groups where COUNT(*) equals the largest count, found with a MAX subquery or a CTE. On the sample data that's Engineering with 13 employees.
Is ORDER BY COUNT(*) DESC LIMIT 1 a correct answer?
Only when there can't be a tie. If two departments share the top count, LIMIT 1 returns one of them and drops the other. Since 2021, Marketing and Sales each hired 3 people here, and LIMIT 1 returns just one.
Can I write MAX(COUNT(*)) in SQL?
Not in SQLite, MySQL, PostgreSQL or SQL Server, which reject one aggregate inside another at the same level. Oracle allows nested aggregates like SELECT MAX(COUNT(*)) FROM employees GROUP BY department_id, but a subquery works everywhere.
How do I show the department name instead of the id?
Join employees to departments on d.id = e.department_id and group by d.id, d.name. Grouping by the id as well keeps two departments with the same name from merging into one row.
Should I use RANK or ROW_NUMBER to find the top department?
Use RANK or DENSE_RANK and filter on rank 1, which keeps every tied department. ROW_NUMBER gives tied rows different numbers, so it behaves like LIMIT 1.

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app

Other SQL dialects

  • SQL Server SELECT TOP 1 WITH TIES ... ORDER BY COUNT(*) DESC keeps every department tied for first. Plain TOP 1 has the same problem as LIMIT 1.
  • PostgreSQL PostgreSQL 13 and later accept FETCH FIRST 1 ROWS WITH TIES after the ORDER BY, which keeps ties without a subquery.

Keep going