SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.
Within each non-null department, keep employees whose DENSE_RANK by salary descending is <= 3. Return department, name, salary, salary_rank. Exclude NULL salaries. Order by department, salary_rank, name.
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
Interview-style drill: DENSE_RANK <= 3 keeps ties inside the top band.
Within each non-null department, keep employees whose DENSE_RANK by salary descending is <= 3. Return `department`, `name`, `salary`, `salary_rank`. Exclude NULL salaries. Order by `department`, `salary_rank`, `name`.