SQL Interview Questions – Common Questions and Answers

interview-question

Given a table 'employees'

 employee_id | salary | department_id 
-------------+--------+---------------

Only using SQL find all the variants of employee-transfers from one department to another, so that average salary in both 'departure' and 'arrival' department grew.

PS I was asked the question on a interview, which never gave an answer, and Google is of little help.

Best Answer

So you are looking for Employees that earn below the average in their current department but above the average in their prospective new department.

One possible way of getting all employee transfers that would meet this would be

WITH departments
     AS (SELECT AVG(salary) AS AvgSalary,
                department_id
         FROM   employees
         GROUP  BY department_id)
SELECT e.employee_id,
       dept_current.department_id AS current_department_id,
       dept_new.department_id     AS new_department_id
FROM   employees e
       JOIN departments dept_current
         ON e.department_id = dept_current.department_id
            AND dept_current.AvgSalary > e.salary
       JOIN departments dept_new
         ON dept_new.AvgSalary < e.salary
Related Question