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;  
    
    • 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:

    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
    • 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?

        • 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

        • 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. — 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

    Please share your solution.

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

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

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

Facebook photo

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

Connecting to %s

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

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

Join 2,469 other followers

Follow me on Twitter

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