Post reply

Are senior SQL developer interview questions too difficult?

  • February 24, 2023 at 10:44 am

    #4153665

    Is my standard for interview questions too high? (Warning: this post is lengthy) TL/DR

    After nearly a year of frustration due to the lack of qualified applicants, I want to make sure we don't have unrealistic expectations for senior developer applicants.

    Background

    We don't require applicants to have experience in business intelligence, as long as they have a good understanding of T-SQL. We will provide coaching and teaching on other topics such as Analysis Services, Dimensional Modeling, etc. All we assess is the applicant's T-SQL knowledge within SQL Server Management Studio.

    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?

  • February 24, 2023 at 12:05 pm

    #4153685

    I read through the interview questions at the link, and yeah, I'd say those are somewhat tough questions. I've been doing this for 30+ years. I've written a few books. I've maintained a blog for 15 years. I honestly feel like I've got some knowledge and experience for real. Yet, I have to look up examples on DENSE_RANK to be sure I'm using it correctly, or even that it's the right solution for a given query. That knowledge isn't just sitting in my head ready to be accessed.

    The list of questions you have seem mostly reasonable. Maybe not the running totals one (again, I just don't have that info in my head) or the XML. The rest are, yeah, reasonable for a senior person to know.

    However, I feel your pain. This is a list of questions I used to ask (well over 10 years ago now, some would be changed with more up-to-date info). As it says in the blog post, this eliminated about 4 out of 5 candidates. I also like open-ended questions where we can talk about how someone approaches problems.

    Finding good people is frankly difficult. I think that's most of what you're hitting. Whether or not some of the interview questions are overly focused on specific T-SQL behaviors or not, yeah, that's fair enough. Heck, if I were interviewing, I'd say that outright, "What? You think that stuff stays in this tiny brain full-time? Nah, I have to look that up." But then I'd explain how I would look it up, test the implementation and then show it works. I'd also go into talking about performance implications, if any, and probably start trotting out execution plans. However, you wouldn't see me regurgitate a lot of really complex T-SQL on demand. Yet, I really do think I'm senior level.

    Maybe I'm wrong. Wouldn't be the first time.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA     Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • February 25, 2023 at 1:53 am

    #4153942

    Previous post deleted.. I just realized that this is spam.

    I also took a look at just one of the questions and answers (question 8)... I didn't look at all the answers for question 8 but it didn't take long to find what many would be considered to be a worst practice and another the clearly demonstrates that the author doesn't actually know what NOT IN does.

    Of course, that's a part of the reason why it actually is difficult to find good Senior SQL Developers.  🙁

    • This reply was modified 3 weeks, 3 days ago by  Jeff Moden. Reason: Sudden realization of what this post really is

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 27, 2023 at 6:15 pm

    #4154712

    None of your questions are out of a senior level person's knowledge and understanding.

    Does 10 years indicate a senior level position?  Maybe not.  The time on a job is no indication of skills.  What was the depth of that 10 years?  I interviewed a "senior database administrator" who had years of experience.  His experience was changing backup tapes.  That's it.

    Now, to be devils advocate, there are more than one way to do something.  Using the FizzBuzz problem, I may write a loop, test it to make sure the logic is correct, and then change it to a set based approach.

    Are you considering HOW they got the question wrong?  If their answer is not what you would have done, do they get to explain why they did their way?

    But, seriously, there are few great candidates out there it seems.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply