Answering a SQL Query Question from a Recruiter
By Bill Graziano
on 28 March 2001
| 7 Comments
| 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.
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.
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
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.