What is a good 'FizzBuzz' question for a SQL programmer?

Question

We are looking to hire a SQL programmer and need a good screening question similar to the FizzBuzz question but for SQL.

While it is certainly possible to write a FizzBuzz solution using SQL, I think the effort is misplaced. The FizzBuzz question assesses coding fundamentals such as looping, conditionals, output, and basic math. With SQL, I think something related to queries, joins, projections, and the like would be more appropriate. But, just as with FizzBuzz, it should be simple enough that 'good' SQL programmers can write a solution on paper in a couple minutes.

What is a good 'FizzBuzz' question for a SQL programmer?

Answer 1

We typically use something like this as a bare minimum for SQL:

Given the tables:

Customers: CustomerID, CustomerName

Orders: OrderID, CustomerID, ProductName, UnitPrice, Quantity

Calculate the total value of orders for each customer showing CustomerName and TotalPrice.

In our view, this is a pretty simple question requiring a join on two tables, grouping, and an aggregate function. We're amazed at how many people we talk to that presumably write database code in their job can't remember join syntax (and we never care which syntax they use, MSSQL style or Oracle style or something else).

What I like about this question is it lends itself to follow up questions like

How would you find all customers that ordered more than $1000 total? How would you normalize these tables? How would you optimize the queries?