Recently, we were looking for a SQL developer that could become part of our team, could develop, and could convert a user story into a solution. We had run into people who look good on paper, but many who had embellished their SQL skills. To combat this I devised a problem for them to solve.

The problem provides a script to generate 2 tables with data, and then describes what the data and the tables represent. Then we create a hypothetic business problem/objective that can be solved with the information in the tables. The "Correct" solution requires multiple levels of knowledge, requires careful reading and checking of the requirements contained within the business problem. A "basic" solution requires about 30-40 minutes of analysis of the data in the tables and analysis of the requirements. I expect the actual solution to take about 10 minutes to write and test. My own solution is a Select statement of about 8 lines involving an inner JOIN, and either a UNION or OR condition in the where clause, an aggregate function, a GROUP BY, and a HAVING.

After initial screening of resumes, we send out the problem to the candidate and ask them to send their solution in 1-2 days before their interview date and be prepared to discuss their solution in-depth during the interview. I make myself available to the candidate by cell phone if they have questions about a nuance contained in the problem at any time before the interview. I review their solution and may test it if it involves an approach that I wasn't anticipating. The candidate is expected to be able to speak about their solution during the interview

This is what I am trying to achieve: 1) Can the candidate figure out how to run the script to load the data. 2) Can the Candidate read and correctly interpret English (my clients are all English speakers). 3) Does the candidate pay attention to details and nuances contained in the business problem. 4) Does the candidate reach out to someone to clarify a requirement or just "assume" they know what a vague requirement really means. 5) Ascertain whether the candidate is a brute force or elegant solution developer. 6) Whether the candidate understands the basic constructs of SQL. 7) Whether the candidate understands more advanced concepts of set theory (how they combine the tables group where clauses). 8) give a common framework for having the candidate discuss a technical solution.

In reality, I'm not at all interested in whether the candidate produces a solution with the 23 rows that a correct solution produces. I'm concerned with all of the thinking, interpreting, and interacting skills. I can teach the language and the business, but I can't teach how to think, interpret, interact, research and pay attention to details.

If you'd like a copy of this exercise, I'd be happy to forward it to you privately.

Jim