Article: SQL Query asked by a recruiter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: SQL Query asked by a recruiter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-11-06 : 19:21:07

Tom writes "During an interview, a recruiter asked me this question saying that it is probably the most difficult SQL query he can think of. I am tempted to say it is not possible to accomplish in a single query... maybe you know . . ." Read on to see what dastardly question the recruiter asked our intrepid student and see if the SQL gurus can answer it.

Article Link.

carldickson
Starting Member

1 Post

Posted - 2002-10-22 : 08:26:01

Hi,
Just saw this.... couldnt you do this??

SELECT open_date, SUM(CASE WHEN open_date <= GETDATE() AND
ISNULL(Close_Date,'2400-01-01') >= GETDATE() THEN
1
ELSE
0
END) AS VOl
FROM BUGS
Group BY open_date
ORDER BY open_date

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-08 : 16:10:44

The issue is the lack of the table of all days. You could create one:

select dateadd(day,d,Convert(DateTime,'01-01-2002')) as MakeDate
FROM
(
SELECT
A + 5 * B + 25 * C + 125 * D as d
FROM
(select 0 as A union select 1 union select 2 union select 3 union select 4) A1
CROSS JOIN
(select 0 as B union select 1 union select 2 union select 3 union select 4) A2
CROSS JOIN
(select 0 as C union select 1 union select 2 union select 3 union select 4) A3
CROSS JOIN
(select 0 as D union select 1 union select 2) A4
) T
WHERE d < 365
order by 1

Ugly, but it works .... you get the idea!

Then, encapsulate the above as a subquery and join it to the bugs table:

SELECT MakeDate as Day, SUM(CASE WHEN OpenDate is null THEN 0 ELSE 1 END) as OpenCases
FROM
(SELECT ... [from above]) AllDays
LEFT OUTER JOIN
Bugs
WHERE AllsDays.MakeDate BETWEEN Bugs.OpenDate and ISNULL(Bugs.CloseDate,'12/31/2059')

That should return a record for every single day, without creating a new table, in 1 select statement.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-10 : 17:50:39

This question is usually given with reference to rooms booked in a hotel or something like that.

Don't think there is any way of doing it in a single query without giving a resriction on the date range or using dynamic sql (constructed from a client app so you can still say it's a single query - but of course you shuoldn't allow that in a system).
Why does it have to be a single query? Probably because there is something dubious about the system design.
Any way of doing this would probably be inefficient so the system should be designed to service it with an aggregate table - probably in a separate reporting database.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-11 : 12:06:34

Actually, these queries are not just logic puzzles for recruiters. I work in the finance dept. for a consulting firm and write queries and reports directly for the CFO, and you'd be surprise the information they want. And the databases that the company uses aren't always custom written to handle every possible query -- they are what they are, you had to deal with it and answer questions. The best example of this is an Accounting system. You can always archive off the data in a warehouse and transform that data for easy reporting, but a) that can take a long time and b) the data will never be truly live.

A great example is we have a customer table listing all customers, the date they joined, and they date they termed (if any). A report is requested with not only members per year, but also rates of change, trends and such. This is very standard and not really an unusal request -- and of course, you wouldn't restructure the entire accounting system just to avoid doing an inefficient cartesian every now and then.

So, the idea of maintaining tables just listing out years or months or dates is actually very useful and common, and I use them all of the time. One more example:

Show all total sales, by month, by office. If a month/office combo has NO sales, return a record with zero. The answer requires of a table of all months you wish to include, cross joined with all offices and that subquery can be left outer joined to a rolled up table of order by office/month.

Anyway, to sum up: It does no good to explain to the higher-ups that you can't print a line that says "May, Sales: $0" because no sales occured in May !!!

Edited by - jsmith8858 on 11/15/2002 17:19:06

Lavos
Posting Yak Master

200 Posts

Posted - 2002-11-11 : 16:08:15

JSmith, the problem is the "single query" requirement. It's simple to do a lot of these things without limiting yourself to a single query. For instance, input the data range and it generates the dates in one part of the script, and then use that range to generate the report.

The silliness is expecting one line of SQL to do everything. If the recruited needs to see that you can generate a query to do something, then he shouldn't tie your hands with this silly crap. If I remember this topic and you don't, and I can do his little trick, does that mean I'm a better SQL person than you? No. It doesn't. Hell, I'm almost iffy about letting people slide with the exact grammar as long as they have all the steps correct besides getting the syntax perfect.

Of course, I'm not hiring, so it's not like I have a say in the matters.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-18 : 08:33:44

A better interviewer would ask how you would approach the problem (allowing you to point out the design problems) and then after a discussion if you were not hopelessly lost and haven't already answered it ask if it's possible in a single query - but by that time the question would probably be unnecessary.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Atmadeep
Starting Member

1 Post

Posted - 2007-11-12 : 11:39:23

I executed the following query in Oracle database and it gives desired results.On a particular date a bug is open if the close date is greater than that date or the close date field is null(in case the bug is not yet closed):

select distinct open_date,(Select count(bugid) from bug where
open_date<=tbl.open_date and (close_date>tbl.open_date or close_date is NULL))"Open Bug Count"
from bug tbl
order by open_date;

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-12 : 13:17:10

Good grief - lets revisit a 5 year old thread.
Unfortunately this only gives results for dates on which a bug has been opened - that's the problem, it's trivial if you ignore that as you have shown.
Now you could join to a derived table for the dates created in a CTE - depends whether that counts as a single statement though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jdub
Starting Member

1 Post

Posted - 2008-05-29 : 16:05:06

In case anyone is still reading this, and since derived tables and sub-queries seem to be rare, maybe someone is.

I saw someone get hired once because the interviewer was impressed that the programmer asked several questions when presented with the standard programming question we used. Most people would just take a stab at it, and then I'm sure go home and complain about management. He turned out to be an exceptional hire.

I'm also fairly sure that I have not been hired many times because I emphasize process. I bring up things like test phases, and prototyping during the interview. I'm getting to where I will ask for concrete evidence of quality processes, because more than once I have been hired with the promise of a good environment only to find the usual, "shut up and write some code" attitude once I got there.

So, my initial reaction to this recruiter was that he didn't know what he was asking and figured anyone who could do this query must be better than anyone who can't. He was just seeing if you would jump through a hoop for him, and he could sell you to the highest bidding hoop holder. With no further information, who knows. As someone looking for work, you have to decide. I would at least ask a couple questions to show that you know how to communicate, and can do more than just the technical side of the job. If you are not desperate for a job, if you are looking for the right fit for you, then use the opportunity to discuss the value of good design, suggest that some testing would improve the likelihood of an optimal solution, discuss the value of using tools designed to improve productivity, or anything else you think is important. It may be just what the interviewer is looking for.

Hrodas
Starting Member

2 Posts

Posted - 2013-11-15 : 19:56:07

Hmm what about following solution:

SELECT
CAL.CalendarDT
,COUNT(DISTINCT BUG.BUG_ID) AS Open_Bugs
FROM
( SELECT DISTINCT
COALESCE(AA.OPEN_DT,BB.CLOSE_DT) AS CalendarDT
FROM BUG AS AA
FULL OUTER JOIN
BUG AS BB
ON (AA.OPEN_DT=BB.CLOSE_DT)
) AS CAL
LEFT JOIN BUG ON (COALESCE(BUG.CLOSE_DT,current_date) > CAL.CalendarDT AND BUG.OPEN_DT <= CAL.CalendarDT)

Still dates won't be continuous, let say during weekend no one open or close Bug.
So my solution will miss those particular dates. But who cares when no one is at work :-)

It is one Query and cover most of possible dates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources