Senior SQL Developer Interview Questions - Too Difficult? [closed]

Question

Warning, this is a long post. TL/DR: Am I being too harsh with my interview questions?

OK, after ~9 months of total disappointment in the applicant pool, I want to make sure we're not expecting too much from our senior developer applicants.

Background

We're a business intelligence shop. However, we don't expect candidates to come in with BI knowledge. If you have a solid T-SQL background, that's all we need. We'll get you the rest of the way there with regards to Analysis Services, Dimensional Modeling, etc. So all we test is T-SQL skills. Everything happens within SSMS.

If candidates pass a phone screening, we bring them in for a coding interview where we give them 7 questions and a generous 90 minutes. I'm present during the coding to watch for various things like how they navigate through the tools, what they research, etc. It should be noted that we make it very clear that any resources are available to them, including the internet, BOL, etc.

Questions

Here are the questions in abbreviated form in an attempt to not give too much away (since they might google this question :P)

1. Fizzbuzz - I want to see how they generate their rowset. If they use variables/loops, I die inside
2. Aggregation - The question basically forces candidates to use the full scope of the select statement (select, from, where, group by, having)
3. How NULLs work - I give a "tricky" question where an IN subquery doesn't return anything because of a NULL value in the subqueried table. I ask for the workaround to make the query work and then an explanation on why it didn't in the first place. I want to know that they understand what a NULL value is/means and how SQL evaluates them.
4. Indexing - A simple 2 join query with no indexes. I ask that candidates improve the entire queries sub-tree cost by 20%. I quickly find out whether or not they've indexed before. I make it a point to inform them "READ THE QUESTION. GIVE ME WHAT I ASK FOR". Sadly, I don't think a single applicant as even noted the beginning or ending sub-tree cost.
5. Running Totals - I give a table with a PK/date/numeric values and ask them to update another column with a running total per PK. This isn't easy and it's a weird task. I don't really care if they finish in time, but I want to see research if they haven't done it or a decent attempt (even if its inefficient, at least start writing some correlated subqueries).
6. XML - I ask them to retrieve a value from a XML-typed column. I don't expect candidates to know how to do this, and I tell them that. The point of this one is to watch them do research, etc., just like any other time they encounter a new problem at work and need to solve it.
7. CTE - I just ask them to write a CTE. That's it. Bonus if it does something creative/interesting.

And that's it. So, my question is how fair are these questions? We're looking for someone with actual experience. Its unfathomable to me that someone with 10 years of SQL experience can't even get 1 or 2 of these questions. Am I being too harsh? Should I add/remove stuff from this?

Answer 1

kewl questions. They are only as fair as what your pass/fail criteria are.. The harder the test the better a guage it is of a candidates abilities... so I see no problem with this set of questions, as long as you don't expect a specific grade or percentage of correct answers...

If you are concerned about a candidates' perception as to how fair the test is, make it a point to tell them up front, that the test is exactly what it is, simply a guage to observe/measure their problem solving skills and ability to think on their feet...