Largest Group

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: easy
Seniority: L5

Problem

Compensation is reviewing the company's high earners, the people paid more than $100,000. Which departments employ the most of them, and how many high earners does each have?

One group towers above the rest.

Tables

employees

Columns: employee_id INTEGER, department TEXT, emp_name TEXT, salary REAL, manager_id INTEGER, hire_date TEXT, user_id INTEGER

employees: 10 of 200 rows
employee_id	emp_name	department	salary	manager_id	hire_date	user_id
1	Alice Chen	Engineering	55731	NULL	2021-02-02	100
2	Bob Martinez	Product	61462	NULL	2022-03-03	197
3	Carol Wu	Design	67193	NULL	2023-04-04	294
4	David Kim	Marketing	72924	NULL	2024-05-05	391
5	Eve Johnson	Sales	78655	NULL	2025-06-06	488
6	Frank Patel	HR	84386	NULL	2026-07-07	585
7	Grace Lee	Finance	55117	NULL	2020-08-08	682
8	Hank Brown	Operations	60848	NULL	2021-09-09	779
9	Iris Davis	Data	66579	NULL	2022-10-10	876
10	Jake Wilson	Legal	72310	NULL	2023-11-11	973

Expected output

First 3 of 3 rows
department	high_earner_count
Legal	12
Marketing	12
Operations	12

Worked solution and explanation

What this problem really is

This is a tie-preserving top-group query wearing a compensation costume. The real question: which departments sit exactly at the maximum number of high earners, when more than one can share that peak? Anyone can count high earners per department. The trap is ORDER BY high_earner_count DESC LIMIT 1, which sorts and grabs one row, silently dropping the departments tied with it. Here Operations, Marketing and Legal each have 12 people paid over $100,000, so LIMIT 1 reports one and buries the other two.

Why a plain GROUP BY isn't enough

A bare GROUP BY department hands back all ten departments with their high-earner counts, not just the leaders. You need a filter that keeps only the groups whose count equals the maximum, which means computing that maximum and comparing against it. Equality is what lets a tie survive; a position filter like LIMIT cannot.

Break down the requirements

Step 1: Filter to high earners first

Keep only high earners with WHERE salary > 100000 before any grouping. Filtering after the count would force awkward gymnastics; filter the rows first.

Step 2: Count high earners per department

Group the survivors by department and take COUNT(*). One row per department with its high-earner headcount.

Step 3: Find the maximum once

A scalar subquery wraps the same filtered GROUP BY and takes MAX(cnt). Computed once, reused in the outer filter.

Step 4: Filter on equality, not on rank

Use HAVING COUNT(*) = (scalar max). Equality keeps every department whose count matches, so Operations, Marketing and Legal all surface.

The solution

DEPARTMENTS TIED AT THE MOST HIGH EARNERS

SELECT department,
       COUNT(*) AS high_earner_count
FROM employees
WHERE salary > 100000
GROUP BY department
HAVING COUNT(*) = (
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM employees
        WHERE salary > 100000
        GROUP BY department
    )
)

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

How would you solve this with a window function instead? (Tests fluency with `RANK() OVER (ORDER BY COUNT(*) DESC)` as a tie-preserving alternative to the scalar `MAX`.)
What changes if the cutoff for a high earner becomes a parameter? (Probes whether the candidate applies the salary filter before grouping rather than inside a HAVING clause.)
What if you wanted the top three departments, ties at position three included? (Tests the jump from `= MAX` to `DENSE_RANK` for top-K-with-ties.)