Answering a SQL Query Question from a Recruiter

By Bill Graziano on 28 March 2001 | Tags: Queries

Earlier we published a question about a SQL query that a recruiter asked a job candidate. I presented a couple of different options but didn't really think it could be done . . . until Alexander Netrebchenko sent me the solution. Here's his email and the script . . . and a job if I had one to give him :)

You can download his script and take a look at it. His email follows.

Dear graz,

Here is one possible solution for the problem described in the article 'SQL Query asked by a recruiter'. It seemed challenging, so I decided to put some time and brain energy on it.

Basically, the solution is based on the "second easiest way", though the task is accomplished in single SELECT without any tables and/or stored procedures.

The idea behind this is quite simple:

Generate the sequence of dates for the given period
Join this sequence with BUGS table and aggregate to get the count

The trick is how to generate the sequence of dates in SELECT statement. To accomplish this, I first generate the sequence of integers from 0 to N using Cartesian product (see the source code), and then use DATEADD function to generate dates.
The source is the complete example on how to do this. Well, the query looks pretty cumbersome, but I hope the recruiter would be happy with that... :)
Last point. The source may not be fully optimized (well, it's just a funny homework), so I would be just happy if you or your readers would recommend some improvements there.
Sincerely,

Alexander Netrebchenko.

Wow! One of the tricks that Alexander used is called derived tables. You can replace a table name in a SELECT query with another complete SELECT statement. An example from Books Online looks like this:

SELECT ST.stor_id, ST.stor_name 
FROM stores AS ST, 

     (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count 
      FROM sales 
      GROUP BY stor_id 
     ) AS SA 

WHERE ST.stor_id = SA.stor_id

Alexander used a derived table to build a "table" of possible dates on the fly. We recently linked to this article about derived tables. Neat stuff.

He also used a cartesian product or CROSS JOIN to generate those dates. In this SELECT statement

SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers 
ORDER BY au_lname DESC

it will return each all the publishers joined to the first author, then all the publishers joined to the second author, etc. This is a great way to generate test data in a hurry.

Thanks Alexander, for the great script and solution and letting me publish it.