The T-SQL Quiz
-
This method is not very elegant but it seems to work quickly.
select getdate()
Declare @sql int
set @sql=1
select @sql
while @sql<100
begin
set @sql=@sql+1
begin
begin
print 'bizzbuzz'
end
else
if @sql % 3=0 and @sql % 5 not in (0)
begin
print 'bizz'
end
else
if @sql % 5=0 and @sql % 3 not in (0)
begin
print 'buzz'
end
else
begin
print @sql
end
end
end
I personally wouldn't agree with using 15 rather than "5 and 3". If we posed the problem using x and y variables rather than numbers then we would be need to work out "x times y" and it would probably be as quick to to use a joint number condition.
Thanks for the test. Only been a DBA for 6 months so it made me feel a little surer about my programming.
-
I just wish it was only people interviewing for their first jobs. We're getting people with 5-8 years of "experience" that don't know the answer. I'd attribute it to nervousness, but the other questions we ask on the technical phone screen are equally simple and once someone tanks the index question, they usually fail the others. Like, "what's the difference between blocking and deadlocking" or "tell me a couple of the reasons a query might recompile" (and that one includes a freeby - rebooting the server). Those three questions blow more people out of the water than I care to think about. Oh, and, we interview them on the phone. I've heard people typing the question as we asked it. If you can't read the answer correctly, find another career.
"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 -
A simple "x times y" wouldn't do the trick in many cases. You'd have to instead find the least common multiple.
Example: 10 and 50. 10 * 50 = 500, but 50, 100, 150, 200, etc, are all valid as well.
Any clever methods for finding the least common multiple in T-SQL?
--
Adam Machanic
whoisactive -
To be fair, a reboot does not trigger a recompile, but rather forces the queries to be re-compiled... A recompile will actually fire the recompile event, whereas a compile (or re-compile as the case may be) will not... subtle difference, I suppose--in both cases the end result is essentially the same--but you might use it to further weed through your candidates
--
Adam Machanic
whoisactive -
All true. Honestly we only allowed it to pass because all the answers we were getting were so damned pathetic. We also allowed people count dropping and recreating the proc as a recompile. Most never even got to adding WITH RECOMPILE to the query let alone all the various & sundry other reasons. Every time I read articles by you, Itzik, Kalen Delany, Ken Henderson, etc., I'm convinced I don't belong in this job. Then I go through 50-60 phone screenings and I get reassurance that I might actually be qualifiied to earn my pay.
"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 -
God-awful loops and cursors. It's all I seem to see these days, and getting people to think about changing them? Even when you can demonstrate 100%, 200%, 500%, or 1000% improvement, people hate to give up those loops and cursors.
-
Hey it's not running on my SQL 4.2 servers! LOFL
-
with
r3 (remainder, format) as (select 3, 'Bizz'),
r5 (remainder, format) as (select 5, 'Buzz'),
r15 (remainder, format) as (select 15, 'BizzBuzz'),
__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(r15.format, r5.format, r3.format, cast(n.num as varchar)) as format
from nums n
left outer join r15 on n.num % r15.remainder = 0
left outer join r5 on n.num % r5.remainder = 0
left outer join r3 on n.num % r3.remainder = 0
-
I think there are some nicer solutions here than mine, but I went with a dumb cte approach that seems to work decent. I was surprised to see how many folks repeated their mod operations.
with nums as
(
select 1 as i
union all
select
i + 1
from nums where i < 100
)
select
case i % 3
when 0 then case i % 5 --%3
when 0 then 'BizzBuzz' --%3, %5
else 'Bizz' end --%3, !%5
else case i % 5 --!%3
when 0 then 'Buzz' --!%3, %5
else convert(varchar(3), i) end --!%3, !%5
end
as [data]
from nums
-
I personally wouldn't agree with using 15 rather than "5 and 3". If we posed the problem using x and y variables rather than numbers then we would be need to work out "x times y" and it would probably be as quick to to use a joint number condition.
Well, so long as you're going to change the rules, then let's change our code to adapt to the rules:
/* Greatest common demominator */
CREATE FUNCTION dbo.GCD(@a INT, @b-2 INT)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN @b-2 <> 0 THEN dbo.GCD(@b, @a % @b-2)
ELSE @a END
END
GO
/* Least common multiple */
CREATE FUNCTION dbo.LCM(@a INT, @b-2 INT)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN @a = 0 OR @b-2 = 0 THEN NULL
ELSE (@a/ dbo.GCD(@a, @b-2)) * @b-2 END;
END
GO
DECLARE @i INT;
SET @i = dbo.LCM(@x, @y);
SELECT CASE WHEN n % @i = 0 THEN 'BizzBuzz'
WHEN n % @x = 0 THEN 'Bizz'
WHEN n % @y = 0 THEN 'Buzz'
END
.../* etc. */
Every time n % @x = 0 you also have to perform the check to make sure n % @y = 0. And the second check will only evaluate to True once every @y times. Using @x = 3 and @y = 5 and 100 numbers from the actual problem, that's something like 27 wasted comparisons of n % 5 = 0. For 1,000,000 rows that's a little more than 266,600 wasted comparisons.
Multiplying it out as shown results in no wasted comparisons, but you do have the overhead of calculating the least common multiple (per Adam Machanic's recommendation) and one variable assignment.
-
I was just gearing the solution to the problem at hand, with the parameters given
The least common multiple is an interesting problem though./* Greatest common demominator */
CREATE FUNCTION dbo.GCD(@a INT, @b-2 INT)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN @b-2 <> 0 THEN dbo.GCD(@b, @a % @b-2)
ELSE @a END
END
GO
/* Least common multiple */
CREATE FUNCTION dbo.LCM(@a INT, @b-2 INT)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN @a = 0 OR @b-2 = 0 THEN NULL
ELSE (@a/ dbo.GCD(@a, @b-2)) * @b-2 END;
END
GO
-
Hmm, I vaguely remember learning how to do this in high school, and Wikipedia isn't helping.
http://en.wikipedia.org/wiki/Least_common_multiple
lcm(a,b) = (a * b) / gcd(a,b)
http://en.wikipedia.org/wiki/Greatest_common_divisor
gcd(a,b) = (a * b) / lcm(a,b)
... so it's a recursive problem?
--
Adam Machanic
whoisactive -
Yes, I just posted the code to calculate LCM and GCD
So I suppose the code needs to change to calculate the LCM and print 'BizzBuzz' for each occurrence of the LCM; which for 5 and 3 is 15. -
Ooo. I sure hope you used a Perl script to write that one out.
Roger L Reid
-
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 ) select @a / gcd.a * @b as lcd from gcd where b = 0
Viewing 15 posts - 61 through 75 (of 309 total)
You must be logged in to reply to this topic. Login to reply