SQL Puzzle – FizzBuzz3d18
June 27, 2018 by Kenneth Fisher
I thought for the end of the month it would be fun to do another T-SQL Puzzle. In this case a slight upgrade of the FizzBuzz puzzle using T-SQL. So here are the rules:
Argh: I made a mistake in my rules so I’m correcting them. New text in italics, removed text
- Create a table with the columns x, y and z, each representing a coordinate in a cube.
- Each coordinate can have values 1-100 and there will be every possible combination of values. (1,1,1) to (100,100,100).
- A fourth column will be created that contains the Fizz/Buzz/FizzBuzz. Name it FizzBuzz for convinence.
- If any coordinate is evenly divisible by 3 then
thatthe FizzBuzz cell contains “Fizz”.
- If any coordinate is evenly divisible by 5 then
thatthe FizzBuzz cell contains “Buzz”.
- If any coordinate
or combination of coordinatesis evenly divisible by both 3 and 5 then the FizzBuzz cell contains “FizzBuzz”.
Optional rules to make it hard .. err .. more fun.
- This must be done in a single query. No creating the table & populating it in multiple steps.
- No using the modulo (%) operator.
- It’s going to be a big table so let’s save some space. Use the smallint datatype for the coordinates and of course varchar(8) for the cells.
- Expand to 1000 values per direction (but you had better have a lot of space).
My solution (which I’ll email if you ask me nicely) took ~45 min with all of the … extras. Now I have a moderately slow laptop ($350 when I bought it 4 years ago) with an HDD. I’m also kind of so-so when it comes to T-SQL so I wouldn’t be surprised if you did better than me. As a comparison, the same thing with just 100 values per direction took ~3 seconds.
Category: DBA Humor, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: microsoft sql server, Puzzle, T-SQL
18 thoughts on “SQL Puzzle – FizzBuzz3d”
Leave a Reply to Phil Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Nice! Let’s try it. One of our customers has got a nice production server somewhere, with quite a few TB’s space left. (just kidding…)
The no module requirement was the trickiest.My proposed solution is a bit slower than it will be with using modulo. SET STATISTICS TIME ON says between 1600 and 1900 ms.But that’s on an old development server.
Nice job 🙂 One small thing, the intent was to get Fizz or Buzz if any of the coordinates are a multiple of 3 or 5. The way you have it it’s checking for the total of the three coordinates. So (3,1,1) should have Fizz but doesn’t, and (1,1,1) does but shouldn’t.
My solution – size 100 : 0 seconds, 500 : 90 seconds, 1000 : 15 minutes
Nice job. The recursive CTE is a bit slow, and you should probably try to come up with something a bit less complex on the modulus replacement (although the speed on that seems ok). On the other hand, you are the only person to have followed the rules as they were. Including myself since that wasn’t what I intended, just what I wrote :D. I’ve corrected the rules but you should still consider yourself the first person to have gotten it right.
Hi Kenneth, I’d be interested to hear what I got wrong in my solution?
Technically nothing. If you look at the original text though, it said to make the column fizz/buzz/fizzbuzz not create a whole new column. Of course, that was my original intention, just not what I actually put. That’s why I changed it. Your solution is about the same speed as mine, certainly a lot more interesting too 🙂
Thanks, I still don’t understand the requirement though – you wanted x,y,z and a fourth column that contains the text Fizz or Buzz or Fizzbuzz didn’t you? 4 columns? That’s what I have
That was what I intended. What I originally said was “If the column has a multiple of x then ….” implying that the column with the original number in it should have the FizzBuzz. Kind of like the original puzzle.
Sorry, I know I’m being a pain and it really doesn’t matter, but…
I take my ability to read requirements and understand them seriously and for some reason I just want to point out that you originally specified smallint for the co-ordinates and varchar(8) for the “cells”, in the section “It’s going to be a big table…”.
That specific requirement made it clear that you indeed wanted four columns.
“You can’t put Fizz in a smallint.” 🙂
Several versions later. Runs quicker and I think its correct…
… just realised I left in the top 100 order by in the subquery so this can be remove to shave a little more time off.
— I’m not sure if I understood that thing about combinations of coordinates. Easy to change.
Final effort. Think this one meets the brief….
I think I missed something, This is what I came up with:
I forgot the distinct, and shortened it a little:
SELECT distinct Top 1000 x, y, z
,IIF(x – ((x / 3) * 3) = 0 OR y – ((y / 3) * 3) = 0 OR z – ((z / 3) * 3) = 0, ‘fizz’, ”) +
IIF(x – ((x / 5) * 5) = 0 OR y – ((y / 5) * 5) = 0 OR z – ((z / 5) * 5) = 0, ‘buzz’, ”) as fizzBuzz
Cross APPLY (
SELECT x = ABS(CHECKSUM(NewId())) % 101
,y = ABS(CHECKSUM(NewId())) % 101
,z = ABS(CHECKSUM(NewId())) % 101
declare @scale int = 100
;with cte as (
select 1 as num
from cte where num < @scale
ISNULL(Case when parsename(x.num/3.0,1)=0 or
then 'Fizz' end, '') +
ISNULL(Case when parsename(x.num/5.0,1)=0 or
then 'Buzz' end, '')
from cte as x
cross join cte as y
cross join cte as z
Please share your solution.
Here is my solution, this was a fun challenge. I know mine isn’t the most performant but it works. Definitely going to be rethinking it though and how to increase performance a bit using other techniques.