# SQL Puzzle – FizzBuzz3d

18

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 struck out.

• 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 that the FizzBuzz cell contains “Fizz”.
• If any coordinate is evenly divisible by 5 then that the FizzBuzz cell contains “Buzz”.
• If any coordinate or combination of coordinates is 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.

## 18 thoughts on “SQL Puzzle – FizzBuzz3d”

1. gserdijn says:

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.

```WITH c AS (
SELECT TOP (100)
CAST(ROW_NUMBER() OVER (ORDER BY o.[name]) AS INT) AS nr
FROM [master]..spt_values o)
,
Grid AS
(
SELECT x.nr AS x, y.nr AS y, z.nr AS z
FROM c AS x
CROSS JOIN c AS y
CROSS JOIN c AS z
)
SELECT
CAST(x AS SMALLINT) AS x,
CAST(y AS SMALLINT) AS y,
CAST(z AS SMALLINT) AS z,
/*
CAST(CASE WHEN (x + y + z) % 3 = 0 AND  (x + y + z) % 5 = 0 THEN 'FizzBuzz'
WHEN (x + y + z) % 3 = 0 THEN 'Fizz'
WHEN (x + y + z) % 5 = 0 THEN 'Buzz'
ELSE ''
END AS VARCHAR(8)) AS results, */ /* modulo, faster */
CAST(CASE WHEN (x + y + z + 0.0) / 3 = (x + y + z) / 3 AND (x + y + z + 0.0) / 5 = (x + y + z) / 5 THEN 'FizzBuzz'
WHEN (x + y + z + 0.0) / 3 = (x + y + z) / 3 THEN 'Fizz'
WHEN (x + y + z + 0.0) / 5 = (x + y + z) / 5 THEN 'Buzz'
ELSE ''
END AS VARCHAR(8)) AS results2 /* no modulo, slower and bit ugly */
INTO _results
FROM Grid;
```
• Kenneth Fisher says:

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.

2. mistermagooo says:

My solution – size 100 : 0 seconds, 500 : 90 seconds, 1000 : 15 minutes

```DECLARE @SCALE INT = 1000;

WITH
SEVEN(N) AS (SELECT 1 FROM (VALUES(0),(1),(2),(3),(4),(5),(6)) A(N)),
TALLY(N) AS (SELECT TOP(@SCALE) ROW_NUMBER() OVER(ORDER BY @@SPID) FROM SEVEN A, SEVEN B, SEVEN C),
BASE(ID, F, B) AS (
SELECT CAST(FIZZ.ID AS SMALLINT) ID, FIZZ.TEXT F, BUZZ.TEXT B
FROM (
SELECT TOP (@SCALE) ROW_NUMBER() OVER(ORDER BY TALLY.N, A.ID) ID, A.TEXT
FROM (VALUES(1,''),(2,''),(3,'F')) A(ID,TEXT)
CROSS JOIN TALLY
ORDER BY TALLY.N, A.ID
) FIZZ(ID,TEXT)
JOIN (
SELECT TOP (@SCALE) ROW_NUMBER() OVER(ORDER BY TALLY.N, A.ID) ID, A.TEXT
FROM (VALUES(1,''),(2,''),(3,''),(4,''),(5,'B')) A(ID,TEXT)
CROSS JOIN TALLY
ORDER BY TALLY.N, A.ID
) BUZZ(ID, TEXT)
ON BUZZ.ID = FIZZ.ID
)
SELECT X=A.ID , Y=B.ID , Z=C.ID , VALUE=
CASE WHEN 'F' IN (A.F,B.F,C.F) THEN 'FIZZ' ELSE '' END +
CASE WHEN 'B' IN (A.B,B.B,C.B) THEN 'BUZZ' ELSE '' END
INTO #RESULT
FROM BASE A, BASE B, BASE C```
3. Phil says:

```WITH cte1 AS
(
SELECT 1 AS val
UNION ALL
SELECT val+1
FROM cte1
WHERE val+1 <= 100
),
cte2 AS (
SELECT   CASE
WHEN CAST(CAST(val AS NUMERIC(5,2))/3 AS VARCHAR(max)) NOT LIKE '%.%[1-9]%'
AND  CAST(CAST(val AS NUMERIC(5,2))/5 AS VARCHAR(max)) NOT LIKE '%.%[1-9]%'
THEN 'FIZZBUZZ'
WHEN CAST(CAST(val AS NUMERIC(5,2))/3 AS VARCHAR(max)) NOT LIKE '%.%[1-9]%'
THEN 'FIZZ'
WHEN CAST(CAST(val AS NUMERIC(5,2))/5 AS VARCHAR(max)) NOT LIKE '%.%[1-9]%'
THEN 'BUZZ'
ELSE CAST(val AS VARCHAR(4)) END AS val
FROM cte1
)
SELECT  x.val AS x, y.val AS y, z.val AS z
INTO #t1
FROM cte2 x
CROSS JOIN cte2 y
CROSS JOIN cte2 z```
• Kenneth Fisher says:

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.

• mistermagooo says:

Hi Kenneth, I’d be interested to hear what I got wrong in my solution?

• Kenneth Fisher says:

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 🙂

• mistermagooo says:

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

• Kenneth Fisher says:

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.

• mistermagooo says:

Hi Kenneth,
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.” 🙂

• Phil says:

Several versions later. Runs quicker and I think its correct…

```IF OBJECT_ID('tempdb..#t1') IS NOT NULL
DROP TABLE #t1;

WITH cte1 AS
(
SELECT
CASE
WHEN (val/3)*3 = val AND (val/5)*5 = val THEN CAST('FIZZBUZZ' AS VARCHAR(8))
WHEN (val/3)*3 = val THEN CAST('FIZZ' AS VARCHAR(8))
WHEN (val/5)*5 = val THEN CAST('BUZZ' AS VARCHAR(8))
ELSE CAST(val AS VARCHAR(8))
END AS val
FROM
(SELECT TOP 100 CAST(ones.n + 10*tens.n + 1 AS SMALLINT) AS val
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)
ORDER BY val ) AS x

)
SELECT  x.val AS x, y.val AS y, z.val AS z
INTO #t1
FROM cte1 x
CROSS JOIN cte1 y
CROSS JOIN cte1 z
OPTION (MAXRECURSION 1000)```
• Phil says:

… 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.

4. Phil_Factor (@Phil_Factor) says:

— I’m not sure if I understood that thing about combinations of coordinates. Easy to change.

```WITH OneToTen
AS (SELECT f.number
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS f (
number
) ),
oneToThousand
AS (SELECT Cast(upToAThousand.counter AS SMALLINT) AS counter,
CASE WHEN Left(Reverse(Cast(upToAThousand.counter AS VARCHAR(4))), 1) IN (
'5' , '0'
)   THEN   'Fizz' ELSE NULL END AS fizz,
CASE WHEN upToAThousand.number IN (3, 6, 9, 12, 15, 18, 21, 24, 27) THEN
'Buzz' ELSE NULL END AS buzz
FROM
(
SELECT a.number + (b.number * 10) + (c.number * 100) + 1 AS counter,
a.number + b.number + c.number + 1 AS number
FROM OneToTen AS a
CROSS JOIN OneToTen AS b
CROSS JOIN OneToTen AS c
) AS upToAThousand )
--SELECT * FROM oneToThousand ORDER BY counter

SELECT d.counter, e.counter, f.counter,
Coalesce(d.fizz, e.fizz, f.fizz, '') + Coalesce(d.buzz, e.buzz, f.buzz, '')
FROM oneToThousand AS d
CROSS JOIN oneToThousand AS e
CROSS JOIN oneToThousand AS f
ORDER BY d.counter + e.counter + f.counter;```
5. Phil says:

Final effort. Think this one meets the brief….

```IF OBJECT_ID('tempdb..#t1') IS NOT NULL
DROP TABLE #t1;

WITH cte1 AS
(
SELECT CAST(ones.n + 10*tens.n + 1 AS SMALLINT) AS val
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n)
,(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)
),
cte2 AS(
SELECT
CASE
WHEN (val/3)*3 = val AND (val/5)*5 = val THEN CAST('X' AS CHAR(1))
WHEN (val/3)*3 = val THEN CAST('F' AS CHAR(1))
WHEN (val/5)*5 = val THEN CAST('B' AS CHAR(1))
END AS fzbz,
val
FROM
cte1
)
SELECT  x.val AS x, y.val AS y, z.val AS z,
CASE
WHEN ('F' IN (x.fzbz,y.fzbz,z.fzbz) AND 'B' IN (x.fzbz,y.fzbz,z.fzbz)) OR ('X' IN (x.fzbz,y.fzbz,z.fzbz)) THEN CAST('FIZZBUZZ' AS VARCHAR(8))
WHEN ('F' IN (x.fzbz,y.fzbz,z.fzbz)) THEN CAST('FIZZ' AS VARCHAR(8))
WHEN ('B' IN (x.fzbz,y.fzbz,z.fzbz)) THEN CAST('BUZZ' AS VARCHAR(8))
END AS FizzBuzz
INTO #t1
FROM cte2 x
CROSS JOIN cte2 y
CROSS JOIN cte2 z
--ORDER BY x, y, z
OPTION (MAXRECURSION 1000)```
6. James says:

I think I missed something, This is what I came up with:

```SELECT Top 1000 x, y, z
,CASE
WHEN x - ((x / 15) * 15) = 0 OR y - ((y / 15) * 15) = 0 OR z - ((z / 15) * 15) = 0 THEN 'fizzBuzz'
WHEN x - ((x / 3) * 3) = 0 OR y - ((y / 3) * 3) = 0 OR z - ((z / 3) * 3) = 0 Then 'fizz'
WHEN x - ((x / 5) * 5) = 0 OR y - ((y / 5) * 5) = 0 OR z - ((z / 5) * 5) = 0 Then 'buzz'
ELSE NULL
END as fizzBuzz
FROM sys.objects
Cross APPLY (
SELECT x = ABS(CHECKSUM(NewId())) % 101
,y = ABS(CHECKSUM(NewId())) % 101
,z = ABS(CHECKSUM(NewId())) % 101
) a```
7. James says:

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
FROM sys.objects
Cross APPLY (
SELECT x = ABS(CHECKSUM(NewId())) % 101
,y = ABS(CHECKSUM(NewId())) % 101
,z = ABS(CHECKSUM(NewId())) % 101
) a

8. Ashish Bhatia says:

declare @scale int = 100

;with cte as (
select 1 as num
union all
select num=num+1
from cte where num < @scale
)
select
x=x.num,
y=y.num,
z=z.num,
FizzBuzz=
ISNULL(Case when parsename(x.num/3.0,1)=0 or
parsename(y.num/3.0,1)=0 or
parsename(z.num/3.0,1)=0
then 'Fizz' end, '') +
ISNULL(Case when parsename(x.num/5.0,1)=0 or
parsename(y.num/5.0,1)=0 or
parsename(z.num/5.0,1)=0
then 'Buzz' end, '')
from cte as x
cross join cte as y
cross join cte as z

9. Ryan Cerney (@Ryancerney) says:

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.

```DECLARE @i int = 1, @numRows INT= 100;

WITH cte AS ( SELECT CAST(@i AS SMALLINT) AS x, CASE WHEN (( @i - ( @i / 3 ) * 3 ) = 0 AND ( @i - ( @i / 5 ) * 5 ) = 0 ) THEN CAST('FizzBuzz' AS VARCHAR(8))
WHEN ( @i - ( @i / 3 ) * 3 ) = 0 THEN CAST('Fizz' AS VARCHAR(8))
WHEN ( @i - ( @i / 5 ) * 5 ) = 0 THEN CAST('Buzz' AS VARCHAR(8)) END AS FizzBuzz
UNION ALL
SELECT CAST(cte.x + 1 AS SMALLINT) AS x, CASE WHEN ((( cte.x + 1 ) - (( cte.x + 1 ) / 3 ) * 3 ) = 0 AND (( cte.x + 1 ) - (( cte.x + 1 ) / 5 ) * 5 ) = 0 ) THEN CAST('FizzBuzz' AS VARCHAR(8))
WHEN (( cte.x + 1 ) - (( cte.x + 1 ) / 3 ) * 3 ) = 0 THEN CAST('Fizz' AS VARCHAR(8))
WHEN (( cte.x + 1 ) - (( cte.x + 1 ) / 5 ) * 5 ) = 0 THEN CAST('Buzz' AS VARCHAR(8)) END AS FizzBuzz
FROM cte
WHERE x &lt; @numRows )
SELECT cte.x AS x, y.x AS y, z.x  AS z, COALESCE(z.FizzBuzz, y.FizzBuzz, cte.FizzBuzz, '') AS FizzBuzz
INTO #recur3d
FROM cte CROSS join (   SELECT c.x, c.FizzBuzz
FROM cte AS c ) AS y
CROSS join (   SELECT c2.x, c2.FizzBuzz
FROM cte AS c2 ) AS z
OPTION (MAXRECURSION 1000)

DROP TABLE #recur3d```

This site uses Akismet to reduce spam. Learn how your comment data is processed.    