19

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.

2
  • 5
    I hope the answer they were expecting was "Why lower case table and column names, and why underscores rather than camel casing" Sep 14 '15 at 12:11
  • @MikeyMouse: why not? Camel case is the preferred way to write table names in SQL (at least where I come from) Dec 1 '15 at 9:10
22

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 
2
  • How do you know which department is "new" and which one is "old"?
    – mustaccio
    Sep 14 '15 at 14:30
  • 1
    @mustaccio - The department they are currently in is in the table employees. This finds all departments that they could transfer to (if any) that meet the condition. Sep 14 '15 at 14:32
10

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:

  • Is the question asking about past transfers or potential future transfers? There is ambiguity in the wording.
  • Are there other fields in this table or is this all of them? If so, what are they?
  • Are there any constraints or indexes defined on this table? Where is the rest of the schema?
  • Is this an OLTP system or OLAP?

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.

11
  • 6
    "all the variants" seems clear to me that they are talking about hypothetical transfers not historic transfers. And then the question is perfectly answerable with the given information. Sep 13 '15 at 17:31
  • 7
    I am with Martin. The question is clear to me and enough information answer the question.
    – paparazzo
    Sep 13 '15 at 17:47
  • 3
    @MartinSmith I am not saying that your interpretation isn't a valid one, or that it isn't the most likely one. I am saying that it isn't the only one. There are often times when the wording of something "seems" clear, but is still incorrect ;-) As I said at the beginning, my answer is in light of this being an interview question, not a test question. And I have often been asked to do things that were "clearly" stated, and yet were entirely not what the person really wanted, but they weren't aware that they were asking for the wrong thing because they assumed everyone agreed on terminology. Sep 13 '15 at 18:05
  • 2
    @edc65 Thank you. Given that my concern is about ambiguity, I appreciate the tongue-in-cheek ambiguity of your comment :). Sep 13 '15 at 20:39
  • 2
    @srutzky I guess I'm the only one who's with you here :) Thank you for adding your answer to the mix though. As someone who has conducted hundreds of interviews, this is the type of thoughtful response I'd be looking for. I probably wouldn't ask this question, but if I did, an ideal candidate would likely respond similarly and then, after clarification, would write a query like Martin's. The main takeaway in reading these comments is that people see things in different ways and make different assumptions. So always clarify and confirm your assumptions, especially in an interview situation! Sep 14 '15 at 0:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.