The T-SQL Quiz
-
declare @a int, @b int select @a = 3, @b = 5 ;with gcd (a, b) as (select @a, @b union all select b, a % b from gcd where b 0), lcd (lcd) as (select @a / gcd.a * @b as lcd from gcd where b = 0), ra (remainder, format) as (select @a, 'Bizz'), rb (remainder, format) as (select @b, 'Buzz'), rlcd (remainder, format) as (select lcd.lcd, 'BizzBuzz' from lcd), __nums (num) as (select null union all select null), _nums (num) as ( select row_number() over (order by [1].num) from __nums as [1], __nums as [2], __nums as [3], __nums as [4], __nums as [5], __nums as [6], __nums as [7] ), nums (num) as (select top (100) cast(num as decimal) from _nums) select coalesce(rlcd.format, rb.format, ra.format, cast(n.num as varchar)) as format from nums n left outer join rlcd on n.num % rlcd.remainder = 0 left outer join rb on n.num % rb.remainder = 0 left outer join ra on n.num % ra.remainder = 0 - Cool test
CreateTable testTable
(num int
, hit char(8)
)
setnocount on
declare@i int
select@i = 1
while@i < 100
begininsert into testTable
(num, hit)
Select @i
, Hit = Case when @i % 3 = 0 and @i % 5 = 0 then 'BizzBuzz'
when @i % 5 = 0 then 'Buzz'
when @i % 3 = 0 then 'Bizz'
else ' '
End
select @i = @i + 1
endselect*
fromtestTable
droptable testTable
-
I was just working on the CTE version of the LCM algorithm when I saw David's post. Anyway, here's what I came up with (please excuse any typos, I had to manually re-type it in here):
DECLARE @a INT;
DECLARE @b-2 INT;
DECLARE @lcm INT;
WITH LCM(a, b, c) AS
(
UNION ALL
SELECT b, a % b, @a / b * @b-2
FROM LCM
WHERE b <> 0
)
SELECT @lcm = MAX(c)
FROM LCM;
-- Again using CTE to produce numbers, can change to a numbers table
WITH Rows(n) AS
(
SELECT 1
UNION ALL
SELECT n + 1
FROM Rows
WHERE n < 100
)
SELECT n, CASE WHEN n % @lcm = 0 THEN 'BizzBuzz'
WHEN n % @a = 0 THEN 'Bizz'
WHEN n % @b-2 = 0 THEN 'Buzz' END
FROM Rows;
-
Looks cool. I'm going to pass it out to the team & see what they think.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
--fewer ctes and joins, more functions...
;with
gcd (a, b) as (select @a, @b-2 union all select b, a % b from gcd where b 0),
lcd (lcd) as (select @a / gcd.a * @b-2 as lcd from gcd where b = 0),
r (remainder, format, [rank]) as (
select @a, 'Bizz', 2 union all
select @b-2, 'Buzz', 2 union all
select lcd.lcd, 'BizzBuzz', 1 from lcd
),
nums (num) as (
select top (100) cast(row_number() over (order by [1].x) as decimal)
from
(select null x union all select null) as [1],
(select null x union all select null) as [2],
(select null x union all select null) as [3],
(select null x union all select null) as [4],
(select null x union all select null) as [5],
(select null x union all select null) as [6],
(select null x union all select null) as [7]
)
select
format
from (
select
n.num,
coalesce(r.format, cast(n.num as varchar)) as format,
rank() over (partition by n.num order by r.[rank]) as [rank]
from nums n
left outer join r
on n.num % r.remainder = 0
) x
where [rank] = 1
order by
num
-
Ha ha...I am the Scott in Grant's original post and he suggested I swing by and take a look at the comments (which for the most part are WAY better than the original article!), and this is no different. It is amazing to see how many people think...THINK..their solution is the ONLY way, or the BEST way or how others have violated the original requirements so on and so on BLAH BLAH BLAH. This was a fun excercise and you should all leave it at that. Of course new ways of doing things are fun to read and use but to sit and debate and debate about it...Come on. I can tell several posters are consultants "working from home". We write SQL - we do NOT cure cancer. Happy coding
-
Actually Grant this was a fun excercise and I appreciate you bringing this up at work. I do suggest others bring this challenge up with your coworkers it was a trip.
-
Actually at one place I worked my databases cured cancer! Well, they were used to control radiation machines!
In that environment, you bet we were discussing any and all changes backwards, forwards and up and down before they were made.
-
Well, 2 comments to this:
1. It would depend on whether or not I believed that it was a typo or a big gaping hole in the applicant's understanding of how SQL works.
2. I would not be interested in hiring someone who's reply was something along the lines of "Yes, I saw that typo when I posted the query but did not bother to update .... "
Making typos is expected. Deciding not to correct a typo that causes the fundamental logic to fail is not acceptable.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Don't forget, there are 2 sides on interview.
And if you've got a really good guy at another side of the table then it's not only you evaluating him.
And if you as a boss prefer loop without typos over proper query with typos I better fail the test and don't waste my time in a company of bad programmers with a bad manager working on doomed projects.
> Making typos is expected. Deciding not to correct a typo that causes the fundamental logic to fail is not acceptable.
What kind of fundamental logic is behind that "100"?
All fundamental logic end at number 15. Everything else is repeating periods.
If it would be "Bozz" instead of "Bizz" would you name it fundamental as well?
This code does not go into production, it does not go anywhere, and nobody's gonna read results from this query.
They only thing is important here - the way of thinking: looping or querying, following the logic, using number 15 not specified in the task, etc.
And you failed to choose the best option.
_____________
Code for TallyGenerator -
Ladies... The signal to noise ratio is starting to drop a bit. This was supposed to be a bit of candy for some intellectual stimulation a little bit different than the usual. By & large it worked very well considering all the contributions, both of yours included. Let's not bicker and argue over who killed who.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Since I use hierarchies in almost every database I've developed several SPs and UDFs to handle the requirements of this test. I keep a "pivot" perm table just for this purpose. It only has 1 col and 10 rows, e.g., 0-9.
To get a table to count from 1 to 100 just self join it once, 1-1000000 self join it 5 times etc. Very fast and scaleable. I've created a small proc to do this which makes the code much cleaner but isn't necessary.
Doing the test from 1 to 1M takes 2.8 ms using the proc and 2.6 with just the code.
During an interview I would setup my solution with a discussion on theory. Show them you can think not just code after all their are looking for a good mind not just a code junkie.
--code without proc
select getutcdate()
SELECT numb,
numbstr = CASE WHEN (numb%3=0 and numb%5=0 ) THEN 'BizzBuzz'
WHEN numb%3=0 THEN 'Bizz'
WHEN numb%5=0 THEN 'Buzz'
ELSE '' END
FROM (
SELECT CAST( a.numb + b.numb + c.numb + d.numb + e.numb AS INT ) +1 AS numb
FROM [dbo].PivotTable AS a , [dbo].PivotTable AS b , [dbo].PivotTable AS c , [dbo].PivotTable AS d , [dbo].PivotTable AS e ) as p
ORDER BY numb
select getutcdate()
--Code with proc
IF EXISTS (SELECT * FROM tempdb.sys.objects
WHERE Name LIKE '#tmpPivotTable%' AND type = 'U')
DROP TABLE #tmpPivotTable
GO
SELECT * INTO #tmpPivotTable FROM dbo.PivotTable WHERE 1=0
INSERT INTO #tmpPivotTable EXECUTE dbo.p_LoadPivotTable 5 --5 creates a million rows
SELECT z.numb,numbstr =
CASE WHEN (numb%3=0 and numb%5=0 ) THEN 'BizzBuzz'
WHEN numb%3=0 THEN 'Bizz'
WHEN numb%5=0 THEN 'Buzz'
ELSE ''
END
FROM #tmpPivotTable z
select getutcdate()
PS How do you get the code to format in this editor?
-
I wasn't attempting to choose or even write the best option. I was merely pointing out that they could get the same result by checking for % 15 as they do by checking for % 3 and % 5.
So I can't fail a test I wasn't taking.
I had been adding the smilies to my posts to indicate that I was being facetious. Perhaps that is the test I failed. That aspect of my posts about your reply obviously failed to reach its intended target.
Maybe if I use more smilies.
And I think you and a couple of others have hit on an excellent point. An interview is only as good as the interviewer. Who's testing the interviewers to see if they qualify to ask the questions???
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
C'mon, it's not an interview, nobody's dead serious.
I just believe it's better to argue and prove (or fail to prove) your point here then make a mistake in real life situation.
I saw you smilies, of course, probably I should put more in my posts as well.
> And I think you and a couple of others have hit on an excellent point. An interview is only as good as the interviewer. Who's testing the interviewers to see if they qualify to ask the questions???
Board?
Financial Director?
After some projects result in a spoon scratching the bottom...
_____________
Code for TallyGenerator -
Gotta love those Monty Python references
"A what? A grailllllll?!"
Viewing 15 posts - 76 through 90 (of 309 total)
You must be logged in to reply to this topic. Login to reply