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.
Group employees into bands: low (below 50,000), mid (50,000 to 99,999) and high (100,000 and above). Return band and employees for every band that has at least one employee, in the order low, mid, high.
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: band, employees.
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',49999,null),(2,'B','Tech',50000,null),(3,'C','HR',100000,null),(4,'D','HR',99999,null),(5,'E','Ops',20000,null);Output
low|2
mid|2
high|1The boundaries: 49,999 is low, 50,000 and 99,999 are mid, 100,000 is high.
Show hints
- A searched CASE returns the first branch whose condition is true.
- Sort by a second CASE (or by min(salary)) to get low, mid, high instead of alphabetical order.
Show the explanation
A searched CASE WHEN salary < 50000 THEN 'low' WHEN salary < 100000 THEN 'mid' ELSE 'high' END labels each row; branches are tried in order, so the second condition does not need a lower bound. Group by the label and count. Alphabetical order would give high, low, mid, so order by min(salary) within each band — or by another CASE that maps labels to 1, 2, 3.
Original UniCircle question. Last reviewed 22 Oct 2026.