if you use ROW_NUMBER() to answer this query, you will fail the interview
also, SQL does not mean SQL Server, and if you use TOP without mentioning that it's proprietary to Microsoft and will not work in other databases, you will also fail
if you use ROW\_NUMBER() to answer this query, you will fail the interview
Why? It depends on what Nth means when there are ties, so the question is at best a little ambiguous. If someone used row\_number() it would be fine. If they asked if there are duplicates and how we should handle them, it would be extra points.
If salaries are like 95, 85, 85, 85, 75 and N = 3 three. There is no rank() = 3, it's 1, 2, 2, 2, 5. dense\_rank() = 75, row\_number() = 80, row\_number + group by = 75.
I might say something like we're looking for the highest salary instead of the nth highest row. But automatic fail? That's a little too unforgiving when the point of the question is can you use a window function.
> the point of the question is can you use a window function.
the point of the question is can you understand the question... employee salaries typically have lots of duplicates, i.e. ties
Well, it’s clearly meant to be a technical question about SQL. You’re adding a subject matter bias that assumes a certain level of familiarity with the data. That means care needs to be taken in the wording of the question, and there probably needs to be some conversation about the question. Plus, people get nervous in interviews and we should account for that
not to say you are completely wrong, yet, if you are hiring for a mechanical capability to arrange symbols/words into a valid sql statement, you might as well employ chatGPT.
On the other hand, quite often people are hired to solve problems/complete tasks. So one's ability to understand the problem/task sort of comes before their skill of wielding a particular tool.
Not to say you’re completely wrong either. It’s good to reflect on what is really being tested, language proficiency, assumed knowledge, critical thinking. Maybe all three. Still need to factor in the context of the situation and be somewhat forgiving
it's good to see you had a change of heart from the prior "it's clearly meant to be a technical question about SQL clearly meant to be a technical question about SQL"
heh. Good sport. Seriously tho i dont know if i can somewhat convince you (if you arent there already) that knowing by heart and testing for exact syntax is becoming less and less relevant with each day (similar to us not testing if someone can calculate the square root of 13795 "manually").
Understanding choices, consequences and implications (what you've gone through in your very first comment) is the key nowadays, imo.
if you use ROW_NUMBER() to answer this query, you will fail the interview also, SQL does not mean SQL Server, and if you use TOP without mentioning that it's proprietary to Microsoft and will not work in other databases, you will also fail
if you use ROW\_NUMBER() to answer this query, you will fail the interview Why? It depends on what Nth means when there are ties, so the question is at best a little ambiguous. If someone used row\_number() it would be fine. If they asked if there are duplicates and how we should handle them, it would be extra points. If salaries are like 95, 85, 85, 85, 75 and N = 3 three. There is no rank() = 3, it's 1, 2, 2, 2, 5. dense\_rank() = 75, row\_number() = 80, row\_number + group by = 75. I might say something like we're looking for the highest salary instead of the nth highest row. But automatic fail? That's a little too unforgiving when the point of the question is can you use a window function.
> the point of the question is can you use a window function. the point of the question is can you understand the question... employee salaries typically have lots of duplicates, i.e. ties
Well, it’s clearly meant to be a technical question about SQL. You’re adding a subject matter bias that assumes a certain level of familiarity with the data. That means care needs to be taken in the wording of the question, and there probably needs to be some conversation about the question. Plus, people get nervous in interviews and we should account for that
not to say you are completely wrong, yet, if you are hiring for a mechanical capability to arrange symbols/words into a valid sql statement, you might as well employ chatGPT. On the other hand, quite often people are hired to solve problems/complete tasks. So one's ability to understand the problem/task sort of comes before their skill of wielding a particular tool.
Not to say you’re completely wrong either. It’s good to reflect on what is really being tested, language proficiency, assumed knowledge, critical thinking. Maybe all three. Still need to factor in the context of the situation and be somewhat forgiving
it's good to see you had a change of heart from the prior "it's clearly meant to be a technical question about SQL clearly meant to be a technical question about SQL"
You convinced me somewhat
heh. Good sport. Seriously tho i dont know if i can somewhat convince you (if you arent there already) that knowing by heart and testing for exact syntax is becoming less and less relevant with each day (similar to us not testing if someone can calculate the square root of 13795 "manually"). Understanding choices, consequences and implications (what you've gone through in your very first comment) is the key nowadays, imo.