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.
Return the department (or departments, if tied) with the highest total salary, with that total as payroll. Order by dept.
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, payroll.
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',70000,null),(2,'B','Tech',30000,null),(3,'C','HR',100000,null),(4,'D','Ops',40000,null);Output
HR|100000
Tech|100000Tech and HR both total 100,000, so both are returned.
Show hints
- Compute totals per department first.
- Compare each total with the maximum total; ORDER BY … LIMIT 1 would drop ties.
Show the explanation
Aggregate per department in a CTE, then keep the rows whose total equals (select max(payroll) from totals). ORDER BY payroll DESC LIMIT 1 is the tempting answer but silently drops ties. rank() over (order by sum(salary) desc) = 1 or >= ALL (...) are other correct ways.
Original UniCircle question. Last reviewed 22 Oct 2026.