Salary Bands
A pay-equity review at Kestrel Labs cannot start from individual salaries — circulating those would be its own scandal. Instead everyone is placed into one of three published bands and the review works with how many people sit in each.
The bands are fixed by policy: below 100000 is junior band; from 100000 up to and including 124999 is mid band; 125000 and above is senior band. Nothing in the table records a band, so each salary has to be turned into its label before anybody can be counted.
employees — one row per person. department_id is the team they sit on, given as the matching id in departments; salary is annual pay in dollars; hire_date is the day they started, written year-month-day; manager_id is the id of the person they report to, and is NULL for anyone who reports to nobody.
| id | name | department_id | salary | hire_date | manager_id |
|---|---|---|---|---|---|
| 1 | Ava Chen | 1 | 145000 | 2021-03-14 | NULL |
| 2 | Ben Ortiz | 1 | 118000 | 2022-06-01 | 1 |
| 3 | Cara Novak | 1 | 121000 | 2023-01-10 | 1 |
| 4 | Deshawn Lee | 2 | 95000 | 2020-09-23 | NULL |
| 5 | Elin Kask | 2 | 88000 | 2022-11-05 | 4 |
| 6 | Farid Amiri | 3 | 76000 | 2023-04-18 | NULL |
| 7 | Grace Kim | 4 | 132000 | 2021-07-30 | NULL |
| 8 | Hugo Silva | 4 | 110000 | 2023-02-14 | 7 |
| 9 | Ines Duarte | 4 | 104000 | 2023-08-01 | 7 |
| 10 | Jonas Weber | 2 | 91000 | 2021-12-19 | 4 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, band and n, one row per band that has at least one person in it, with band holding exactly the text junior band, mid band or senior band and n holding how many employees fall in it. Order the rows by n, largest first, and break ties alphabetically by band. A band nobody falls into produces no row.
Example output — shape only, on invented tallies. The band text is exactly as the task specifies it, lower case and with the word band:
| band | n |
|---|---|
| senior band | 19 |
| junior band | 12 |
| mid band | 6 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in