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.
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
Given that this was an interview question (and not a test question), there are a few possibilities depending on context.
The question is incomplete as stated and cannot perhaps should not be answered in its current form (please see UPDATE section below). What is missing? Well, for example:
If this is more of an OLTP table, then there should be a PK / Unique Index / Unique constraint defined on the employee_id field. And in that case, there would only be one entry per employee_id and hence no way to determine transfers (i.e. there is no "old" department_id record).
If this is more of an OLAP table, then this could be a Slowly-Changing Dimension, in which case there would be multiple employee_id records. But, there would also need to be ValidFrom and ValidTo DATE / DATETIME fields so that departure and arrival departments can be determined in their proper sequence. Without these fields there is no way to determine which department is the departure and which one is the arrival. And not knowing that distinction would allow for getting records back that are the opposite of the request.
So, that "context" for how to interpret this question is the reason why the question is stated like it is.
You forgot some details between the interview and asking it here:
It happens, but if this is the case, then either you need to update the question to fill in the missing info, or it shall remain unanswerable (at least in terms of getting a meaningful answer).
The question has been accurately transcribed here, and these issues were not known to, or intended by, the interviewer(s):
In this case, if you were aware of these issues and they were expecting an answer, then you can use this as a means of weeding them out as a possible employer ;-).
The question has been accurately transcribed here, and these issues were known to, or intended by, the interviewer(s):
In this case, they were probably using this as a means of weeding people out by looking at more than raw technical ability. It is often very important to ask questions to be very clear on the project you are working on since most end-users and product owners, etc don't think / speak in low-level technical details and will often leave out necessary pieces. It is important not to assume but instead to go back to the source of the request to get clarification so that you don't waste time working in the wrong direction.
Remember that you are not interviewing for a position to simply answer technical questions in a vacuum. You are interviewing for a position to work on projects and there will always be ambiguities and/or misleading information in what one is requested to do. A good interviewer will try to get an understanding of both your skill level and if you will actually be productive. I have asked questions just like this when interviewing people to weed out people who do well answering technical questions but would need too much hand-holding and would end up slowing down the team.
UPDATE:
Just to clarify the clarification for those that feel that this is a simple query skill question, interpreted as @Martin has done in his answer: we don't even know if this is the exact wording of the question that was presented to the O.P. But we do know, in as much as we can trust the situation, that this was given in an interview. And good interviewers ask question that not only draw out a candidates technical skill, but also their non-technical / "soft" skills. It could very well be that Martin is correct in his interpretation that the question is asking about potential future transfer combinations (i.e. "sometimes a cigar is just a cigar"). And if this were a test question, then I would be surprised if his answer was not correct. But, this isn't a test question. Sure, it could be an interview question asked by someone who isn't trying to see what type of person the candidate is and how they would perform in a design meeting where such ambiguities come up more often than most people even notice. But no answer was given, so why discount the possibility that this was an experienced interviewer who is looking for candidates who are both smart and gets things done (search on the page for "You’re looking for people who", but you should really read the whole thing). So, between two candidates who are equal in all ways, but one assumed the interpretation and was correct, while the other asked questions and then got the correct answer, I would definitely go with the one who asked first.
