SQL data engineering interview problem. Difficulty: intermediate. Pattern: Joins. About 12 minutes. Part of the Pro drill bank.
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.
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
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`.