Bucket employees by salary using CASE
Return every employee ID and a salary band: Unknown for NULL, Low below 150, Medium from 150 through 249, and High from 250 upward. Return only the requested columns and follow the specified ordering. This question probes an ordered case expression assigns mutually exclusive bands and handles null explicitly.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
id, salary_band; ascending id.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Ana',10,NULL,100),(2,'Ben',10,1,200),(3,'Cara',20,1,200),(4,'Dan',20,3,300),(5,'Eve',NULL,NULL,NULL);
Expected result
id | salary_band 1 | Low 2 | Medium 3 | Medium 4 | High 5 | Unknown
Another dataset (1)
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Solo',10,NULL,100),(2,'Twin',10,1,100),(3,'Null',NULL,NULL,NULL);
Expected result
id | salary_band 1 | Low 2 | Low 3 | Unknown
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Check NULL before numeric comparisons.
- Use increasing upper bounds for the remaining branches.
Target complexity: time O(n) · space O(1)