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.

Category: DBA Humor, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: , ,

»
«

18 thoughts on “SQL Puzzle – FizzBuzz3d”

  1. gserdijn says:
    June 28, 2018 at 4:34 AM

    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;
    Reply
    • Kenneth Fisher says:
      June 28, 2018 at 11:27 AM

      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.

      Reply
  2. mistermagooo says:
    June 29, 2018 at 2:52 AM

    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
    Reply
  3. Phil says:
    July 4, 2018 at 7:56 AM

    How about…

    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
    Reply
    • Kenneth Fisher says:
      July 4, 2018 at 11:00 AM

      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.

      Reply
      • mistermagooo says:
        July 4, 2018 at 6:09 PM

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

        Reply
        • Kenneth Fisher says:
          July 4, 2018 at 7:27 PM

          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:
          July 4, 2018 at 7:36 PM

          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:
          July 4, 2018 at 7:47 PM

          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:
          July 5, 2018 at 1:22 AM

          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:
      July 5, 2018 at 3:44 AM

      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)
      Reply
      • Phil says:
        July 5, 2018 at 3:48 AM

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

        Reply
  4. Phil_Factor (@Phil_Factor) says:
    July 5, 2018 at 5:10 AM

    — 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;
    Reply
  5. Phil says:
    July 5, 2018 at 7:51 AM

    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)
    Reply
  6. James says:
    July 5, 2018 at 3:11 PM

    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
    Reply
  7. James says:
    July 5, 2018 at 4:33 PM

    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

    Reply
  8. Ashish Bhatia says:
    July 10, 2018 at 2:50 AM

    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

    Please share your solution.

    Reply
  9. Ryan Cerney (@Ryancerney) says:
    August 3, 2018 at 12:13 PM

    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
    Reply

Leave a Reply to Phil_Factor (@Phil_Factor) Cancel reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

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

Follow me via RSS

RSS Feed RSS - Posts

RSS Feed RSS - Comments

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

@sqlstudent144

Archives

Categories

Meta

ToadWorld Pro of the Month November 2013
%d bloggers like this: