Salary category buckets

SQL data engineering interview problem. Difficulty: beginner. Pattern: Filtering. About 8 minutes. Free to practice.

HR wants to group employees into pay bands for a report. Look at each employee's salary and give them a label. Use these rules: 'Low' if the salary is below 50000, 'Medium' if it is from 50000 up to and including 100000, 'High' if it is above 100000, and 'Unknown' if the salary is missing (NULL). Return name, salary and salary_category, sorted by name.

Requirements

  • Return 1 row for each employee.
  • Add a column salary_category with one of the labels: Low, Medium, High or Unknown.
  • 50000 and 100000 are both Medium.
  • A NULL salary gets the label Unknown.
  • Sort by name.

Constraints

  • The table is employees.
  • Return only these 3 columns, in this order: name, salary, salary_category.
  • Use CASE WHEN to create the label.

Examples

Input: employees name | salary Alice Chen | 120000 Bob Kumar | 95000 Max Reed | NULL Omar Ali | 105000 Output: name | salary | salary_category Alice Chen | 120000 | High Bob Kumar | 95000 | Medium Max Reed | NULL | Unknown Omar Ali | 105000 | High Why this passes: NULL must be handled explicitly or it falls through every comparison. No Low salaries exist in this seed.

Topics: lakebench, sql, case, case when.

More SQL interview questions · All interview problems · Learn data engineering

beginner

Salary category buckets

Interview-style drill: CASE WHEN to bucket salaries into Low, Medium, High, Unknown.

Categorize each employee salary: - 'Low' if salary < 50000 - 'Medium' if salary between 50000 and 100000 inclusive - 'High' if salary > 100000 - 'Unknown' if salary IS NULL Return `name`, `salary`, `salary_category`. Order by `name`.