Recruitment test

Background *

A couple of days ago (read: early new years eve) I looked through a couple of job ads on (the very ugly) vakanser.se website and stumbled upon (ergh) an ad from swedish firm Multisoft Solutions. While pondering over the solution I thought to myself "hey, why not put it on there". Said and done, here it is:

Problem *

Imagine the following procedure in T-SQL:

CREATE PROCEDURE dbo.ObscureProcedure
    @i int
AS
BEGIN
    SELECT
        CASE
            WHEN @i < 193 THEN
                COUNT(*) * @i
            ELSE
                COUNT(*) * 193
        END AS ObscureCount
    FROM
        VacationGadget
    WHERE
        @i < 886
END

The logic should be simple enough to follow.

The task at hand, is to figure what the following code would return:

long sum = 0;
for (int i = 0; i < 891; i++)
{
    sum += GetIntFromSQL("EXEC dbo.ObscureProcedure @i = " + i.ToString());
}

return sum;

*Side note: That piece of code is horrible. Never, ever do something like it. Thank you.

Now, running the following statement (in the sql server):

EXEC dbo.ObscureProcedure @i = 496

Would give you the answer

ObscureCount
------------
1999287

(1 row(s) affected)

Solution *

Now, thing is, they've pretty much already given us the answer. The steps from here to a solution is pretty clear:

1. Convert the SQL procedure into a function

2. Test the function

3. ??

4. Profit (or not..)

Let's start with the test. Set this up in a unit test (I used nunit, but use whatever you want):

Assert.AreEqual(1999287, dbo.ObscureProcedure(496));

This (of course) will fail the first time it's run, but worry not. We'll soon make it spew green into your eyes. Since the procedure uses COUNT(*) we need to calculate the number of rows the table's got. Our procedure doesn't use @i unless it's lt 193 so we need to 1999287 / 193 which'll give us 10359.

The next step in our path to glory would be tracing the control flow in the procedure, and rebuild it (in your language of choice):

public static int ObscureProcedure(int i)
{
    const int rows = 10359 //remember?

    if (i >= 886) //WHERE @i < 886
        return 0;

    if (i < 193) //CASE WHEN @i < 193 THEN COUNT(*) * @i
        return rows * i;
    else
        return rows * 193;
}

And that should be pretty self-explanatory. Simply put:

1. The WHERE clause in SQL must be evaluated first since that's what the SQL server does

2. Continue as in the original procedure

That's it. You could probably do things in another way, like using a switch-case and some clever subtraction or multiplication. But why bother? Besides, using stuff like that in production environments would just be stupid. But if you do, you should probably switch profession.