Top three salaries with ties

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.

A company wants to recognise its best-paid people in every department, including people who tie. 'Top three salaries' here means the three highest different salary amounts, and everyone who earns one of them. For each department that is not NULL, keep the employees whose DENSE_RANK by salary (highest first) is 3 or less. Return department, name, salary and salary_rank. Ignore NULL salaries. Sort by department, salary_rank and name.

Requirements

• Rank the salaries in each department with DENSE_RANK, from highest to lowest.
• Keep rows with a rank of 3 or less.
• People with the same salary share the same rank, so a department can return more than three people.
• Sort by department, salary_rank, then name.

Constraints

• The table is employees.
• Use DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) in a CTE and filter salary_rank <= 3 after.
• Exclude NULL departments and NULL salaries.

Examples

Input: employees (Engineering) name | salary Alice Chen | 120000 Dan Park | 110000 Bob Kumar | 95000 Eve Ng | 95000 Output: department | name | salary | salary_rank Engineering | Alice Chen | 120000 | 1 Engineering | Dan Park | 110000 | 2 Engineering | Bob Kumar | 95000 | 3 Engineering | Eve Ng | 95000 | 3 Why this passes: Bob and Eve tie at dense rank 3, so both stay. All four Engineering salaries fall within dense ranks 1–3.

Topics: lakebench, sql, dense_rank, ties.

More SQL interview questions · All interview problems · Learn data engineering