The T-SQL Quiz

  • declare @a int, @b int
    select @a = 3, @b = 5
    ;with
       gcd (a, b) as (select @a, @b union all select b, a % b from gcd where b  0),
       lcd (lcd) as (select @a / gcd.a * @b as lcd from gcd where b = 0),
       ra (remainder, format) as (select @a, 'Bizz'),
       rb (remainder, format) as (select @b, 'Buzz'),
       rlcd (remainder, format) as (select lcd.lcd, 'BizzBuzz' from lcd),
       __nums (num) as (select null union all select null),
       _nums (num) as ( 
          select row_number() over (order by [1].num)
          from 
             __nums as [1], 
             __nums as [2], 
             __nums as [3], 
             __nums as [4], 
             __nums as [5], 
             __nums as [6], 
             __nums as [7]
       ),
       nums (num) as (select top (100) cast(num as decimal) from _nums)
    select coalesce(rlcd.format, rb.format, ra.format, cast(n.num as varchar)) as format
    from nums n
    left outer join rlcd on n.num % rlcd.remainder = 0
    left outer join rb on n.num % rb.remainder = 0
    left outer join ra on n.num % ra.remainder = 0
  • Cool test
    Create

    Table testTable

    (num int

    , hit char(8)

    )

    set

    nocount on

    declare

    @i int

    select

    @i = 1

    while

    @i < 100

    begin

    insert into testTable

    (num, hit)

    Select @i

    , Hit = Case when @i % 3 = 0 and @i % 5 = 0 then 'BizzBuzz'

    when @i % 5 = 0 then 'Buzz'

    when @i % 3 = 0 then 'Bizz'

    else ' '

    End

    select @i = @i + 1

    end
    select

    *

    from

    testTable

    drop

    table testTable

  • I was just working on the CTE version of the LCM algorithm when I saw David's post.  Anyway, here's what I came up with (please excuse any typos, I had to manually re-type it in here):

    DECLARE @a INT;

    DECLARE @b-2 INT;

    SELECT @a = 3, @b-2 = 5;

    DECLARE @lcm INT;

    WITH LCM(a, b, c) AS

    (

        SELECT @a, @b-2, 0

        UNION ALL

        SELECT b, a % b, @a / b * @b-2

        FROM LCM

        WHERE b <> 0

    )

    SELECT @lcm = MAX(c)

    FROM LCM;

    -- Again using CTE to produce numbers, can change to a numbers table

    WITH Rows(n) AS

    (

        SELECT 1

        UNION ALL

        SELECT n + 1

        FROM Rows

        WHERE n < 100

    )

    SELECT n, CASE WHEN n % @lcm = 0 THEN 'BizzBuzz'

                   WHEN n % @a = 0 THEN 'Bizz'

                   WHEN n % @b-2 = 0 THEN 'Buzz' END

    FROM Rows;

     

  • Looks cool. I'm going to pass it out to the team & see what they think.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • --fewer ctes and joins, more functions...

    declare @a int, @b-2 int

    select @a = 3, @b-2 = 5

    ;with

    gcd (a, b) as (select @a, @b-2 union all select b, a % b from gcd where b 0),

    lcd (lcd) as (select @a / gcd.a * @b-2 as lcd from gcd where b = 0),

    r (remainder, format, [rank]) as (

    select @a, 'Bizz', 2 union all

    select @b-2, 'Buzz', 2 union all

    select lcd.lcd, 'BizzBuzz', 1 from lcd

    ),

    nums (num) as (

    select top (100) cast(row_number() over (order by [1].x) as decimal)

    from

    (select null x union all select null) as [1],

    (select null x union all select null) as [2],

    (select null x union all select null) as [3],

    (select null x union all select null) as [4],

    (select null x union all select null) as [5],

    (select null x union all select null) as [6],

    (select null x union all select null) as [7]

    )

    select

    format

    from (

    select

    n.num,

    coalesce(r.format, cast(n.num as varchar)) as format,

    rank() over (partition by n.num order by r.[rank]) as [rank]

    from nums n

    left outer join r

    on n.num % r.remainder = 0

    ) x

    where [rank] = 1

    order by

    num

  • Ha ha...I am the Scott in Grant's original post and he suggested I swing by and take a look at the comments (which for the most part are WAY better than the original article!), and this is no different.  It is amazing to see how many people think...THINK..their solution is the ONLY way, or the BEST way or how others have violated the original requirements so on and so on BLAH BLAH BLAH.  This was a fun excercise and you should all leave it at that.  Of course new ways of doing things are fun to read and use but to sit and debate and debate about it...Come on.   I can tell several posters are consultants "working from home".  We write SQL - we do NOT cure cancer.  Happy coding

  • Actually Grant this was a fun excercise and I appreciate you bringing this up at work.  I do suggest others bring this challenge up with your coworkers it was a trip.

  • Actually at one place I worked my databases cured cancer!  Well, they were used to control radiation machines!

    In that environment, you bet we were discussing any and all changes backwards, forwards and up and down before they were made.

  • Well, 2 comments to this:

    1. It would depend on whether or not I believed that it was a typo or a big gaping hole in the applicant's understanding of how SQL works.

    2. I would not be interested in hiring someone who's reply was something along the lines of "Yes, I saw that typo when I posted the query but did not bother to update .... "

    Making typos is expected. Deciding not to correct a typo that causes the fundamental logic to fail is not acceptable.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Don't forget, there are 2 sides on interview.

    And if you've got a really good guy at another side of the table then it's not only you evaluating him.

    And if you as a boss prefer loop without typos over proper query with typos I better fail the test and don't waste my time in a company of bad programmers with a bad manager working on doomed projects.

    > Making typos is expected. Deciding not to correct a typo that causes the fundamental logic to fail is not acceptable.

    What kind of fundamental logic is behind that "100"?

    All fundamental logic end at number 15. Everything else is repeating periods.

    If it would be "Bozz" instead of "Bizz" would you name it fundamental as well?

    This code does not go into production, it does not go anywhere, and nobody's gonna read results from this query.

    They only thing is important here - the way of thinking: looping or querying, following the logic, using number 15 not specified in the task, etc.

    And you failed to choose the best option.

    _____________
    Code for TallyGenerator

  • Ladies... The signal to noise ratio is starting to drop a bit. This was supposed to be a bit of candy for some intellectual stimulation a little bit different than the usual. By & large it worked very well considering all the contributions, both of yours included. Let's not bicker and argue over who killed who.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Since I use hierarchies in almost every database I've developed several SPs and UDFs to handle the requirements of this test. I keep a "pivot" perm table just for this purpose. It only has 1 col and 10 rows, e.g., 0-9.

    To get a table to count from 1 to 100 just self join it once, 1-1000000 self join it 5 times etc. Very fast and scaleable. I've created a small proc to do this which makes the code much cleaner but isn't necessary.

    Doing the test from 1 to 1M takes 2.8 ms using the proc and 2.6 with just the code.

    During an interview I would setup my solution with a discussion on theory. Show them you can think not just code after all their are looking for a good mind not just a code junkie.


    --code without proc

    select getutcdate()

    SELECT numb,

    numbstr = CASE WHEN (numb%3=0 and numb%5=0 ) THEN 'BizzBuzz'

    WHEN numb%3=0 THEN 'Bizz'

    WHEN numb%5=0 THEN 'Buzz'

    ELSE '' END

    FROM (

    SELECT CAST( a.numb + b.numb + c.numb + d.numb + e.numb AS INT ) +1 AS numb

    FROM [dbo].PivotTable AS a , [dbo].PivotTable AS b , [dbo].PivotTable AS c , [dbo].PivotTable AS d , [dbo].PivotTable AS e ) as p

    ORDER BY numb

    select getutcdate()


    --Code with proc

    IF EXISTS (SELECT * FROM tempdb.sys.objects

    WHERE Name LIKE '#tmpPivotTable%' AND type = 'U')

    DROP TABLE #tmpPivotTable

    GO

    SELECT * INTO #tmpPivotTable FROM dbo.PivotTable WHERE 1=0

    INSERT INTO #tmpPivotTable EXECUTE dbo.p_LoadPivotTable 5 --5 creates a million rows

    SELECT z.numb,numbstr =

    CASE WHEN (numb%3=0 and numb%5=0 ) THEN 'BizzBuzz'

    WHEN numb%3=0 THEN 'Bizz'

    WHEN numb%5=0 THEN 'Buzz'

    ELSE ''

    END

    FROM #tmpPivotTable z

     

    select getutcdate()

     

    PS How do you get the code to format in this editor?

     

  • I wasn't attempting to choose or even write the best option. I was merely pointing out that they could get the same result by checking for % 15 as they do by checking for % 3 and % 5.

    So I can't fail a test I wasn't taking.

    I had been adding the smilies to my posts to indicate that I was being facetious. Perhaps that is the test I failed. That aspect of my posts about your reply obviously failed to reach its intended target.

    Maybe if I use more smilies.

     

    And I think you and a couple of others have hit on an excellent point. An interview is only as good as the interviewer. Who's testing the interviewers to see if they qualify to ask the questions???


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • C'mon, it's not an interview, nobody's dead serious.

    I just believe it's better to argue and prove (or fail to prove) your point here then make a mistake in real life situation.

    I saw you smilies, of course, probably I should put more in my posts as well.

    > And I think you and a couple of others have hit on an excellent point. An interview is only as good as the interviewer. Who's testing the interviewers to see if they qualify to ask the questions???

    Board?

    Financial Director?

    After some projects result in a spoon scratching the bottom...

    _____________
    Code for TallyGenerator

  • Gotta love those Monty Python references   "A what?  A grailllllll?!"

Viewing 15 posts - 76 through 90 (of 309 total)

You must be logged in to reply to this topic. Login to reply