HardWindow Functions High Frequency

Department Top Three Salaries

Frequently Asked By:MetaAmazonGoogleBloomberg

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

A company wants to identify high earners in each department. A high earner in a department is defined as an employee who has a salary in the top 3 unique salaries for that department. Write a query to find the employees who are high earners in each of the departments.

Why Technical Interviewers Ask This

FAANG interviewers love this problem because it tests whether you know how to compute partitioned group rankings inside SQL rather than fetching all rows and doing business logic in Python/Java. It also tests understanding of tie resolution in salary bands.

Common Pitfalls & Interview Traps

Using correlated subqueries with COUNT(DISTINCT) which runs in O(N^2) time instead of modern window functions running in O(N log N).

Filtering with WHERE rnk <= 3 directly in the same SELECT statement where DENSE_RANK() is computed (SQL window functions cannot be referenced in their own WHERE clause).

Forgetting to join the department table to retrieve the human-readable department name.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH DepartmentSalaryRanks AS (
  SELECT
    d.name AS department_name,
    e.name AS employee_name,
    e.salary,
    DENSE_RANK() OVER (
      PARTITION BY e.department_id 
      ORDER BY e.salary DESC
    ) AS salary_rank
  FROM employees e
  INNER JOIN departments d ON e.department_id = d.id
)
SELECT
  department_name,
  employee_name,
  salary
FROM DepartmentSalaryRanks
WHERE salary_rank <= 3
ORDER BY department_name ASC, salary DESC;

Approach Breakdown

We create a CTE named DepartmentSalaryRanks that computes DENSE_RANK() partitioned by department_id. In the outer query, we filter WHERE salary_rank <= 3. If four employees in Engineering all make the exact same top salary, all four will be assigned rank 1 and all four will be included.

Edge Cases to Verbally Clarify with Your Interviewer

A department has fewer than 3 employees (all must be returned).

Multiple employees tie for the 3rd rank (all tied employees must be included).

A department has no employees (excluded via INNER JOIN).

Frequently Asked Follow-Up Questions

Why can’t we put WHERE DENSE_RANK() <= 3 directly in the main SELECT?

In the SQL logical query processing order, the WHERE clause evaluates BEFORE window functions (which evaluate in the SELECT / OVER phase). Thus, window function results can only be filtered in an outer query or CTE.

What happens if 5 people in the same department earn $150k at rank 1?

With DENSE_RANK(), all 5 employees receive rank 1. If someone else makes $140k, they receive rank 2. Filtering WHERE rank <= 3 returns all 5 people from rank 1 plus ranks 2 and 3.