Salary grade from a band table

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Joins. About 12 minutes. Part of the Pro drill bank.

A company has a table of pay grades. Each grade (L1, L2 and so on) covers a range of salaries, from min_salary to max_salary, and both ends are included. You want to show each employee's grade. For every employee in employees, find the grade whose band contains their salary. Employees whose salary is above every band, or whose salary is NULL, must still appear, with a NULL grade. Return employee_id, name, salary and grade, sorted by employee_id.

Requirements

  • Return every employee, exactly once.
  • Match each employee to the band where salary BETWEEN min_salary AND max_salary.
  • Use a LEFT JOIN so employees without a band stay in the result.
  • An employee without a matching band gets a NULL grade.
  • Sort by employee_id.

Constraints

  • The tables are employees and salary_bands.
  • Bands do not overlap, and the limits are inclusive.
  • Return only these 4 columns, in this order: employee_id, name, salary, grade.

Examples

Input: employees employee_id | name | department | department_id | salary | hire_date | manager_id 1 | Alice Chen | Engineering | 1 | 120000 | 2022-01-15 | NULL 2 | Bob Kumar | Engineering | 1 | 95000 | 2023-03-01 | 1 3 | Dan Park | Engineering | 1 | 110000 | 2021-11-20 | 1 4 | Eve Ng | Engineering | 1 | 95000 | 2024-01-05 | 2 5 | Omar Ali | Sales | 2 | 105000 | 2020-06-01 | 1 6 | Max Reed | Sales | 2 | NULL | 2023-12-01 | 5 salary_bands grade | min_salary | max_salary L1 | 60000 | 99999 L2 | 100000 | 109999 L3 | 110000 | 119999 Output: employee_id | name | salary | grade 1 | Alice Chen | 120000 | NULL 2 | Bob Kumar | 95000 | L1 3 | Dan Park | 110000 | L3 4 | Eve Ng | 95000 | L1 5 | Omar Ali | 105000 | L2 6 | Max Reed | NULL | NULL Why this passes: Bob and Eve (95000) are L1, Omar (105000) is L2, Dan (110000) is L3. Alice (120000) is above every band and Max has no salary, so both get NULL.

Input: employees employee_id | name | salary 1 | Ann | 60000 2 | Bo | 99999 3 | Cy | 100000 salary_bands grade | min_salary | max_salary L1 | 60000 | 99999 L2 | 100000 | 109999 Output: employee_id | name | salary | grade 1 | Ann | 60000 | L1 2 | Bo | 99999 | L1 3 | Cy | 100000 | L2 Why this passes: Salaries on both limits of the same band (60000 and 99999) land in that band.

Topics: lakebench, sql, range join, left join, null.

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

intermediate

Salary grade from a band table

Interview-style drill: Attach each employee's grade by finding the band that contains their salary.

`salary_bands` lists grades with an inclusive `min_salary` and `max_salary`. Return every employee with the grade whose band contains their `salary`. Employees whose salary is outside every band, or whose salary is NULL, are still returned with a NULL `grade`. Columns: `employee_id`, `name`, `salary`, `grade`. Order by `employee_id`.