Table employees(id int primary key, name text, dept text, salary int, manager_id int references employees(id)); manager_id is null for staff with no manager.
For each department, return the employees whose salary is among the two highest distinct salaries in that department (so ties are all included). Return dept, name, salary, ordered by dept, then salary descending, then id.
Constraints
- Write one SELECT statement for PostgreSQL 16.
- Output columns and row order must match exactly.
Input and output
- Input
- The setup script in the test case creates and fills the tables described above.
- Output
- Columns: dept, name, salary.
Example
Input
create table employees (id int primary key, name text not null, dept text not null, salary int not null, manager_id int references employees(id));
insert into employees values (1,'A','Tech',90000,null),(2,'B','Tech',90000,null),(3,'C','Tech',80000,null),(4,'D','Tech',70000,null),(5,'E','HR',50000,null);Output
HR|E|50000
Tech|A|90000
Tech|B|90000
Tech|C|80000In Tech, A and B share the top level and C is the second level; D is third. HR has one employee.
Show hints
- DENSE_RANK gives tied rows the same rank and leaves no gaps.
- Window functions cannot be used in WHERE; rank in a subquery or CTE first.
Show the explanation
dense_rank() over (partition by dept order by salary desc) numbers salary levels within each department: equal salaries share a rank and the next level gets the next number. Compute it in a CTE, then keep rows with rank ≤ 2. rank() would skip numbers after ties (1, 1, 3) and row_number() would split ties arbitrarily — choosing between the three is the real interview question.
Original UniCircle question. Last reviewed 22 Oct 2026.