Sup all,

I'm putting together a problem for a candidate who rates himself as an intermediate developer, and I'm wondering, what would you do to test his capacity for SQL shenanigans? At the moment I'm aiming for a query that involves some basic knowledge of when to use what join, and how to use HAVING to refine a search.

Too easy? Too hard? Better ideas?

Has he been working with SQL in his previous job? Reason I ask is that I used to do quite a bit of complex SQL in an older job, but after having only written a few SQL queries in the past ~2 years I know I would have to use Google to check up on a few things if I was asked to write anything more than a basic query and have it be correct. If it were me I would go for something more abstract than writing SQL that would show you if he was aware of when to use joins etc. Could be wrong though as I haven't had to interview anyone before so don't have experience on what should be expected.

I should clarify, we're a team with a large component of data warehousing and complex queries, which all our candidates know - so SQL's not a general nice to have, it's really important to have a decent grounding in. As for Googling, that's entirely fine. It's a live coding exercise, if people get stuck, I suggest that they google - I like seeing if they know what to search for. Or if they're too prideful to Google, which wastes time in real life.

Edward Diego wrote: I should clarify, we're a team with a large component of data warehousing and complex queries, which all our candidates know - so SQL's not a general nice to have, it's really important to have a decent grounding in. As for Googling, that's entirely fine. It's a live coding exercise, if people get stuck, I suggest that they google - I like seeing if they know what to search for. Or if they're too prideful to Google, which wastes time in real life.

That makes a bit more sense then, I assumed by the intermediate developer title that it was not a main part of the job and wasn't sure if you would allow them access to the internet. Would you minus points if they used Bing

Yeah, we want to try to keep our checks as close to actual work environment as possible. So we might ask you to draw a system architecture on a whiteboard (if you're claiming that as a strength), but we won't ask you to write code on a whiteboard - and likewise, we pair with them on the tasks because that's how we work.

Running SQL queries shouldn't be hard for anyone and not something I'd ask anyone who isn't straight out of school.

I'd draw a large complex but perfect database schema and ask him what's wrong with it, telling him there is at least three errors (even though theres not).

See what he says - Its also a good test of how he will handle being under pressure with you watching him expecting him to find a problem with it.

Edward Diego wrote: I should clarify, we're a team with a large component of data warehousing and complex queries, which all our candidates know - so SQL's not a general nice to have, it's really important to have a decent grounding in. As for Googling, that's entirely fine. It's a live coding exercise, if people get stuck, I suggest that they google - I like seeing if they know what to search for. Or if they're too prideful to Google, which wastes time in real life.

I'm glad you do this, I'm sure it was someone else here I had a large disagreement with about this practise... The ability to solve the problem quickly and correctly is all I care about. Don't care how you got to that even if you just copied the code from stackoverflow as long as you stand behind it as being the correct answer with no major way of doing it better.

Privoxy wrote: Running SQL queries shouldn't be hard for anyone and not something I'd ask anyone who isn't straight out of school.

You'd be surprised how quickly people forget it. We absolutely require some skills in this regard, so we test for it. It's filtered developers in the past. Developers who are currently employed and contracting on government services.

Incidentally, the query I'm planning on asking them to write is along these lines. For a given simple schema:

customer: id name
sales: customer_id day amount

Give me a list of all my customers, show me their best day earning over 1000 in the last seven days, and lastly, I want the query to tell me if the customer qualifies for our special bonus rate - to qualify for this, you must have had sales over $10,000 on at least two days in the last seven days.

So, expected results. customer_name, best_day, best_day_amount, bonus_qualified

I'd be keen to see if that really is too easy. If you can write this query, then I'm satisfied that you'll do okay with our db work.

Privoxy wrote: I'd draw a large complex but perfect database schema and ask him what's wrong with it, telling him there is at least three errors (even though theres not).

Not really applicable to us, most of our schema is denormalised to allow for fast aggregation at our desired levels of granularity.

Privoxy wrote: See what he says - Its also a good test of how he will handle being under pressure with you watching him expecting him to find a problem with it.

Bah, I hate questions like this in an interview. Also - if we're working under pressure, we've planned our sprint wrong.

Edward Diego wrote: Incidentally, the query I'm planning on asking them to write is along these lines. For a given simple schema: customer: id name sales: customer_id day amount Give me a list of all my customers, show me their best day earning over 1000 in the last seven days, and lastly, I want the query to tell me if the customer qualifies for our special bonus rate - to qualify for this, you must have had sales over $10,000 on at least two days in the last seven days. So, expected results. customer_name, best_day, best_day_amount, bonus_qualified I'd be keen to see if that really is too easy. If you can write this query, then I'm satisfied that you'll do okay with our db work.

Were you expecting subqueries in the answer? Because (unless I'm being an idiot) I don't think this is achievable with a simple JOIN and GROUP BY. Aggregation can get you the best_day_amount, but not the best_day.

Egor wrote: Were you expecting subqueries in the answer?

Yep.

If anyone wants to have a blat:

http://sqlfiddle.com/#!12/99952

I would like a query that returns me the names of all my customers, along with their best top-selling day within the last 7 days, and how much money they made on that day. A top-selling day is a day where the customer makes more than $1000. I also want to know if they should qualify for our special bonus for the best sellers. To qualify, they must have had at least two top-selling days in the last seven days.

I expect the following results for the given dataset, when I run the query. (The expected dates are relative to today, obviously).

Code NAME DAY AMOUNT QUALIFIES_FOR_BONUS Henry Morgan May, 21 2013 00:00:00+0000 8000 1 Edward Teach May, 19 2013 00:00:00+0000 1100 0 William Kidd (null) (null) 0 John Rackham (null) (null) 0