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.