SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL Query asked by a recruiter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/06/2000 :  19:21:07  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 11/10/2000 :  14:46:21  Show Profile  Reply with Quote
SQL Query

Can we not use the following query?



select count(DISTINCT b1.bug_id) as Open, count(DISTINCT b2.bug_id) as Closed
from bugs b1, bugs b2
where b1.opendate <= givendate and
b2.opendate > givendate


The above query assumes that the bug_id is unique. Can be very slow if the bugs table is long since this generates a cross product of all the rows in the table and takes the distinct values.
Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/10/2000 :  16:24:38  Show Profile  Reply with Quote
How about this...

This solution (unfortunately) relies on a date table, but it works well:

SELECT A.Date, 'Open Bugs'=Count(*)
FROM
(SELECT D.Date, B.* FROM Dates AS D LEFT JOIN Bugs AS B
ON (D.Date>=B.OpenDate AND
D.Date<=IsNull(B.CloseDate,convert(datetime,'1/1/2100')))
WHERE D.Date Is Not Null)
AS A
GROUP BY A.Date

The join syntax is unusual in that you are testing for greater than or equal to instead of the usual equality only. I haven't tested it but I think this syntax also works with Oracle.

Null CloseDate's are handled by the IsNull() function, which uses an arbitrary way-in-the-future date to replace the null and qualify for the join expression.

It may be possible to substitute a SELECT statement for the Dates table that somehow generates all of the dates needed, but I can't think of one without using a procedure of some kind. (perhaps a nested dynamic EXECUTE containing a WHILE loop?)

Hope this answers it.

-Rob Volk
robvolk@hotmail.com

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 11/10/2000 :  17:10:29  Show Profile  Visit graz's Homepage  Reply with Quote
Re: First comment

What is this givendate that you refer to? They need the results for all dates, not just one date. A single date is easy. It's running this for all dates in a single query that is hard.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/13/2000 :  12:27:11  Show Profile  Reply with Quote
Re: Sql Query asked by recruiter

Let's assume that we want the histogram to show the bug counts for
each month (maybe a big assumption, but work with me here). The
following query will do the trick. That's assuming that you're
using a real database like Oracle...


select sum(bug_count), month from (
select 0 bug_count, month from (
select 'JANUARY' month from dual union
select 'FEBRUARY' month from dual union
select 'MARCH' month from dual union
select 'APRIL' month from dual union
select 'MAY' month from dual union
select 'JUNE' month from dual union
select 'JULY' month from dual union
select 'AUGUST' month from dual union
select 'SEPTEMBER' month from dual union
select 'OCTOBER' month from dual union
select 'NOVEMBER' month from dual union
select 'DECEMBER' month from dual) months
union
select count(1) bug_count, rtrim(to_char(bugs.open_date,'MONTH')) month
from bugs
where to_char(bugs.open_date,'MONTH') != to_char(bugs.close_date,'MONTH')
group by to_char(bugs.open_date,'MONTH'))
group by month

Peace man,
DM

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/29/2000 :  05:17:23  Show Profile  Reply with Quote
Good question,

I played with a similar question for aircrew availability. startdate and end changed for crew rest but... basically the same.

An ideal item would be a sproc, give it a date and increment type and get the data....

spGetBugs(30, 'DD')
1. Determine increment type
2. setup a cursor for to loop for each date
3. Loop through and UNION the thing together

Another solution please email to wwxxyyzz1234@home.com

Go to Top of Page

carldickson
Starting Member

1 Posts

Posted - 10/22/2002 :  08:26:01  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/08/2002 :  16:10:44  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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.



Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/10/2002 :  17:50:39  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/11/2002 :  12:06:34  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

Lavos
Posting Yak Master

USA
200 Posts

Posted - 11/11/2002 :  16:08:15  Show Profile  Reply with Quote
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!"
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/18/2002 :  08:33:44  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

Atmadeep
Starting Member

1 Posts

Posted - 11/12/2007 :  11:39:23  Show Profile  Reply with Quote
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;
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/12/2007 :  13:17:10  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 11/12/2007 13:19:38
Go to Top of Page

jdub
Starting Member

USA
1 Posts

Posted - 05/29/2008 :  16:05:06  Show Profile  Reply with Quote
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.

Go to Top of Page

Hrodas
Starting Member

2 Posts

Posted - 11/15/2013 :  19:56:07  Show Profile  Reply with Quote
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.

Edited by - Hrodas on 11/15/2013 20:09:47
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000