Write a query to return the top 3 highest-paid employees in each department.

How to think about it

This is one of the most common SQL interview problems, and it is really testing three things at once: whether you know window functions, whether you know why you need a wrapper query, and whether you can reason about ties. Let’s build the answer step by step.

Why you need a wrapper query

Window functions are evaluated in the SELECT phase — after WHERE has already run. So you cannot write WHERE rn <= 3 in the same query that computes rn. You must wrap the ranked result in a CTE or subquery and filter in the outer query.

Step 1: assign a rank per department

SELECT
    dept,
    name,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY dept
        ORDER BY salary DESC
    ) AS rn
FROM employees;

PARTITION BY dept restarts the counter for each department. ORDER BY salary DESC gives rank 1 to the highest earner in each department.

Step 2: wrap and filter

WITH ranked AS (
    SELECT
        dept,
        name,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY dept
            ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT dept, name, salary
FROM ranked
WHERE rn <= 3
ORDER BY dept, salary DESC;

Run it yourself — and try changing ROW_NUMBER to DENSE_RANK to see what happens when salaries tie:

ROW_NUMBER vs DENSE_RANK vs RANK

Requirement	Function
Exactly N rows per dept, ties broken arbitrarily	ROW_NUMBER
Include all employees tied for Nth place	DENSE_RANK
Include ties but skip ranks after them	RANK

With DENSE_RANK and <= 3, if three people share the third salary you get all three — potentially more than 3 rows per department. That may or may not be what the business wants; clarify before you code.

The pre-window alternative (older shops still ask)

SELECT e1.*
FROM employees e1
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.dept = e1.dept
      AND e2.salary > e1.salary
) < 3;

This is O(n²) and fragile — state that you know it, then pivot immediately to the window function version.