June 6, 2013 at 8:53 am
Jeff Moden (6/6/2013)
Heh... FizzBuzz... my old friend during interviews. I don't ask about FizzBuzz on phone interviews. I do use it as one of the questions on the pre-interview written (5 question) test. It tells me almost everything I need to know about a candidate. Things like did they take their time to make the handwritten code legible or did they thoughtlessly scribble it out? Did they take the time to avoid hardcoding of the "100" to make the code more usable? If they did use a WHILE loop or {gasp!} rCTE to solve the problem, was it actually properly written?Out of the last 20 developers I've interviewd, NONE of them has used a setbased method or a Tally Table. I can train people to be smarter but I can't fix stupid (as opposed to being ignorant of techniques), arrogant, or lazy. Getting the best answer for FizzBuzz isn't the reason why I ask the question (although it would be a real pleasure if someone actually did it right, for a change). It's all the other stuff you can learn from it even if someone has memorized the "correct" answer.
Please just shoot me if I ever have to sit on the other side of the interview table from you Jeff. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 11:44 am
Sean Lange (6/6/2013)
But what do you mean by "But then the iztec way avoids disk reads"? Do you mean by creating the tally table on the fly?
Yes, according to the data from: http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]
image: http://www.sqlservercentral.com/Images/10071.gif
June 6, 2013 at 11:52 am
Jeff Moden (6/6/2013)
Out of the last 20 developers I've interviewd, NONE of them has used a setbased method or a Tally Table. I can train people to be smarter but I can't fix stupid (as opposed to being ignorant of techniques), arrogant, or lazy. Getting the best answer for FizzBuzz isn't the reason why I ask the question (although it would be a real pleasure if someone actually did it right, for a change). It's all the other stuff you can learn from it even if someone has memorized the "correct" answer.
What job are you doing this for? I would have thought a mid-level developer would get more difficult questions & it'd be a bit harsh expecting a junior to do it in the exact way you want to do it (especially if you don't specify "without a loop"); rather having the right idea with 1/2 syntax errors being acceptable (nobody hand-writes code).
What you need to bare in mind (aimed at juniors in this case) is you're in an interview; they're going to want to go with the safe option of a loop rather than risk getting it wrong/having to write 9 "UNION ALL" commands & they don't have SSMS to debug their code or Google to search for faster performance alternatives (and I guess limited time).
June 6, 2013 at 12:12 pm
Dird (6/6/2013)
Jeff Moden (6/6/2013)
Out of the last 20 developers I've interviewd, NONE of them has used a setbased method or a Tally Table. I can train people to be smarter but I can't fix stupid (as opposed to being ignorant of techniques), arrogant, or lazy. Getting the best answer for FizzBuzz isn't the reason why I ask the question (although it would be a real pleasure if someone actually did it right, for a change). It's all the other stuff you can learn from it even if someone has memorized the "correct" answer.What job are you doing this for? I would have thought a mid-level developer would get more difficult questions & it'd be a bit harsh expecting a junior to do it in the exact way you want to do it (especially if you don't specify "without a loop"); rather having the right idea with 1/2 syntax errors being acceptable (nobody hand-writes code).
What you need to bare in mind (aimed at juniors in this case) is you're in an interview; they're going to want to go with the safe option of a loop rather than risk getting it wrong/having to write 9 "UNION ALL" commands & they don't have SSMS to debug their code or Google to search for faster performance alternatives (and I guess limited time).
We've had a similar experience with Jeff, but for us, it's only about 20% of them pass the fizzbuzz test. (Mid-level SQL developers)
Note that we actually gave them a Numbers table as part of the question. They just had to write (or tell us over the phone) this part
select case when num % 3 = 0 and num % 5 = 0 then 'x'
when num % 3 = 0 then 'y'
when num % 5 = 0 then 'z'
from numbers
this can easily be done over the phone. a correct answer over the phone would be something like 'i would use a case statement and use modulus to check the remainder is 0.' something along those lines would suffice because shockingly, most people suggest using a while loop or i've even heard someone say 'this can be done using a cursor but I don't like to use those' (really??). one person mentioned using 3 if and select statements. one guy didn't even know modulo. We used to ask this in the In-Person but we were getting too many poor candidates so we now ask this during the phone screen.
It's why I'm asking for more basic query questions over the phone. Finding duplicates in a table also is a good easy one to ask over the phone.
June 6, 2013 at 12:18 pm
Dird (6/6/2013)
Sean Lange (6/6/2013)
But what do you mean by "But then the iztec way avoids disk reads"? Do you mean by creating the tally table on the fly?Yes, according to the data from: http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]
Ok that is what I thought you meant. The way I handle this to use a view so I don't have to code this over and over.
create View Tally as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
This gives you the best of both worlds. You have the advantage of a tally table, it is persistent in that it can be queried just like a permanent table AND it is fast. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 12:31 pm
Sean Lange (6/6/2013)
This gives you the best of both worlds. You have the advantage of a tally table, it is persistent in that it can be queried just like a permanent table AND it is fast. 😀
Assuming it's used often enough that the view definition doesn't have to be pulled from disk 😛
June 6, 2013 at 12:41 pm
Dird (6/6/2013)
Sean Lange (6/6/2013)
This gives you the best of both worlds. You have the advantage of a tally table, it is persistent in that it can be queried just like a permanent table AND it is fast. 😀Assuming it's used often enough that the view definition doesn't have to be pulled from disk 😛
True but even if it has to read the definition from disk that is really fast, faster than reading from a permanent table. You can always write it each time you need it but honestly that is really one of the points of a view in the first place, so you don't have to write the same thing over and over.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 12:48 pm
Sean Lange (6/6/2013)
You can always write it each time you need it but honestly that is really one of the points of a view in the first place, so you don't have to write the same thing over and over.
That is true my Kansas brethren 😛
June 6, 2013 at 12:52 pm
Sean Lange (6/6/2013)
Dird (6/6/2013)
Sean Lange (6/6/2013)
This gives you the best of both worlds. You have the advantage of a tally table, it is persistent in that it can be queried just like a permanent table AND it is fast. 😀Assuming it's used often enough that the view definition doesn't have to be pulled from disk 😛
True but even if it has to read the definition from disk that is really fast, faster than reading from a permanent table. You can always write it each time you need it but honestly that is really one of the points of a view in the first place, so you don't have to write the same thing over and over.
I have written it manually so many times I have it committed to memory.
June 6, 2013 at 12:55 pm
Lynn Pettis (6/6/2013)
I have written it manually so many times I have it committed to memory.
I wouldn't have the need to use it that much & only have about 256K RAM 🙁
June 6, 2013 at 1:01 pm
Dird (6/6/2013)
Sean Lange (6/6/2013)
You can always write it each time you need it but honestly that is really one of the points of a view in the first place, so you don't have to write the same thing over and over.That is true my Kansas brethren 😛
You in Kansas too?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 1:03 pm
Sean Lange (6/6/2013)
You in Kansas too?
Nope. The old country 😛
June 6, 2013 at 1:45 pm
Dird (6/5/2013)
floresg2 (6/5/2013)
He understood using a case statement but he had never used modulo (%). This was a guy with years of supposed SQL experience.
How often have you used modulo in genuine work code (not quiche)? web scripting doesn't count. I don't see why you would ask such a question. It's a long over-used programming question which now only tests whether a person has Googled answers to interview questions to try & deceive you.
The most difficult thing about a FizzBuzz question is the acting involved in pretending that you're actually thinking of the answer.
Thank you. I have never needed to use modulo. It's actually that simple. When I need a function, I go looking for one and if there isn't one, I build it myself.
I've been sql'ing since v6.5 and I'm sure there are a lot of other functions I've never needed, and may therefore not know of their existence, and certainly not their name.
June 6, 2013 at 3:01 pm
Ask them::
Given table1 with columns "id" and "value", where each id has 1 to 3 values/rows in the table:
Describe the essentials of a single query (no subquery, CTE, etc.) to find the min, max and mid values for each id.
If only 1 value/row for an id, return just min, others NULL; if 1 or 2 values, return min and max, others NULL; if three, return min, max and mid.
[Note: this is much harder than it sounds! ... so you should get to hear them "working aloud" as they talk their way thru it]
Edit: added "for each id" to "Describe" sentence.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 6, 2013 at 3:12 pm
ScottPletcher (6/6/2013)
Ask them::Given table1 with columns "id" and "value", where each id has 1 to 3 values/rows in the table:
Describe the essentials of a single query (no subquery, CTE, etc.) to find the min, max and mid values for each id.
If only 1 value/row for an id, return just min, others NULL; if 1 or 2 values, return min and max, others NULL; if three, return min, max and mid.
[Note: this is much harder than it sounds! ... so you should get to hear them "working aloud" as they talk their way thru it]
Edit: added "for each id" to "Describe" sentence.
MID?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 31 through 45 (of 66 total)
You must be logged in to reply to this topic. Login to reply