Interview SQL question
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 &gt; e.salary
       JOIN departments dept_new
         ON dept_new.AvgSalary &lt; e.salary