Filter Engineering salaries

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

The employees table has 1 row for every person in the company. Your manager wants a short list of well-paid people in the Engineering team. Write a query that returns every employee who works in the Engineering department and earns more than 80,000. Show these columns in this order: employee_id, name, department, salary and hire_date. Sort the result so the highest salary comes first. If two people earn the same salary, the person with the smaller employee_id comes first.

Requirements

  • Keep only the rows where department is exactly 'Engineering'.
  • Keep only the rows where salary is greater than 80000. Someone who earns exactly 80000 does not count.
  • A row must pass both rules, so join them with AND.
  • Sort by salary from high to low. When salaries are equal, sort by employee_id from low to high.

Constraints

  • The table is called employees. Department names are written in normal title case, like 'Engineering' and 'Sales'.
  • Return only these 5 columns, in this order: employee_id, name, department, salary, hire_date.
  • salary is a number and hire_date is a date.

Examples

Input: employees employee_id | name | department | salary | hire_date 1 | Alice Chen | Engineering | 120000 | 2022-01-15 2 | Bob Kumar | Engineering | 95000 | 2023-03-01 3 | Dan Park | Engineering | 110000 | 2021-11-20 4 | Eve Ng | Engineering | 95000 | 2024-01-05 5 | Omar Ali | Sales | 105000 | 2020-06-01 6 | Max Reed | Sales | NULL | 2023-12-01 Output: employee_id | name | department | salary | hire_date 1 | Alice Chen | Engineering | 120000 | 2022-01-15 3 | Dan Park | Engineering | 110000 | 2021-11-20 2 | Bob Kumar | Engineering | 95000 | 2023-03-01 4 | Eve Ng | Engineering | 95000 | 2024-01-05 Why this passes: Omar is over 80000 but Sales. Max has NULL salary. All four Engineering rows clear the salary bar.

Topics: lakebench, sql, where, filter.

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

beginner

Filter Engineering salaries

Interview-style drill: Filter employees in Engineering earning more than 80000.

From `employees`, return every row where `department = 'Engineering'` and `salary > 80000`. Columns: `employee_id`, `name`, `department`, `salary`, `hire_date`. Order by `salary` descending, then `employee_id`.