June 6, 2013 at 3:29 pm
Sean Lange (6/6/2013)
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?
Sorry: middle value.
So, if the values were 10, 50, 100: 10 is min, 100 is max, 50 is mid.
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:31 pm
Sean Lange (6/6/2013)
MID?
Yeah, the Q as a whole is a bit meh. I assume he means AVG (to go with the MIN/MAX functions) which I don't see as a very good test (min/max are common use and decrypting the words is more difficult)...if it means median then it's a dodgy question asking for no subquery & in an interview scenario :s
June 6, 2013 at 3:32 pm
ScottPletcher (6/6/2013)
Sean Lange (6/6/2013)
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?
Sorry: middle value.
So, if the values were 10, 50, 100: 10 is min, 100 is max, 50 is mid.
I'm not sure I can do this without a subquery or CTE and I consider myself a good SQL developer.
June 6, 2013 at 3:33 pm
ScottPletcher (6/6/2013)
Sorry: middle value.So, if the values were 10, 50, 100: 10 is min, 100 is max, 50 is mid.
What's the solution then?
June 6, 2013 at 3:33 pm
Dird (6/6/2013)
Sean Lange (6/6/2013)
MID?Yeah, the Q as a whole is a bit meh. I assume he means AVG (to go with the MIN/MAX functions) which I don't see as a very good test (min/max are common use and decrypting the words is more difficult)...if it means median then it's a dodgy question asking for no subquery & in an interview scenario :s
No, I don't mean avg, I mean middle.
Min/max may be common to some, not so common to others; this is not just a basic grouping q, altho it is that also.
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:36 pm
ScottPletcher (6/6/2013)
Dird (6/6/2013)
Sean Lange (6/6/2013)
MID?Yeah, the Q as a whole is a bit meh. I assume he means AVG (to go with the MIN/MAX functions) which I don't see as a very good test (min/max are common use and decrypting the words is more difficult)...if it means median then it's a dodgy question asking for no subquery & in an interview scenario :s
No, I don't mean avg, I mean middle.
Min/max may be common to some, not so common to others; this is not just a basic grouping q, altho it is that also.
The big deal with this question is that your data is limited to 3 values. Do not expand on the possible number of entries (IE: 4/5/100) and a reasonable solution is within grasp.
I remember a conversation about a year ago regarding this and I personally consider it a trick question. I don't build things with expected # of row limiters, I expect requirements to change and I expect to store n rows, not n where n must be 3 or less.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 6, 2013 at 3:43 pm
Evil Kraig F (6/6/2013)
ScottPletcher (6/6/2013)
Dird (6/6/2013)
Sean Lange (6/6/2013)
MID?Yeah, the Q as a whole is a bit meh. I assume he means AVG (to go with the MIN/MAX functions) which I don't see as a very good test (min/max are common use and decrypting the words is more difficult)...if it means median then it's a dodgy question asking for no subquery & in an interview scenario :s
No, I don't mean avg, I mean middle.
Min/max may be common to some, not so common to others; this is not just a basic grouping q, altho it is that also.
The big deal with this question is that your data is limited to 3 values. Do not expand on the possible number of entries (IE: 4/5/100) and a reasonable solution is within grasp.
I remember a conversation about a year ago regarding this and I personally consider it a trick question. I don't build things with expected # of row limiters, I expect requirements to change and I expect to store n rows, not n where n must be 3 or less.
That would be revealing in an interview as well: I don't solve problems I don't like.
My current company works for clients. We push back as much as we can, but at times we have to give in to something they want.
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:47 pm
Luis Cazares (6/6/2013)
ScottPletcher (6/6/2013)
Sean Lange (6/6/2013)
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?
Sorry: middle value.
So, if the values were 10, 50, 100: 10 is min, 100 is max, 50 is mid.
I'm not sure I can do this without a subquery or CTE and I consider myself a good SQL developer.
Nevermind, it's actually quite simple, but got stucked with the usual way to go.
June 6, 2013 at 3:49 pm
ScottPletcher (6/6/2013)
Evil Kraig F (6/6/2013)
ScottPletcher (6/6/2013)
Dird (6/6/2013)
Sean Lange (6/6/2013)
MID?Yeah, the Q as a whole is a bit meh. I assume he means AVG (to go with the MIN/MAX functions) which I don't see as a very good test (min/max are common use and decrypting the words is more difficult)...if it means median then it's a dodgy question asking for no subquery & in an interview scenario :s
No, I don't mean avg, I mean middle.
Min/max may be common to some, not so common to others; this is not just a basic grouping q, altho it is that also.
The big deal with this question is that your data is limited to 3 values. Do not expand on the possible number of entries (IE: 4/5/100) and a reasonable solution is within grasp.
I remember a conversation about a year ago regarding this and I personally consider it a trick question. I don't build things with expected # of row limiters, I expect requirements to change and I expect to store n rows, not n where n must be 3 or less.
That would be revealing in an interview as well: I don't solve problems I don't like.
My current company works for clients. We push back as much as we can, but at times we have to give in to something they want.
I agree with giving the client a solution. I don't agree that it's required to enforce the limiter and have to recode when performing a more robust solution takes about as much mental resources and allows for expansion.
It's not solving a problem I don't like, I personally feel the rules are twisted into an invalid context and that you have to rely on business restrictions. It can be coded for both their current usage as well as exceptions later, but not with the artificial restriction of no subqueries.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 6, 2013 at 3:54 pm
Evil Kraig F (6/6/2013)
ScottPletcher (6/6/2013)
Evil Kraig F (6/6/2013)
ScottPletcher (6/6/2013)
Dird (6/6/2013)
Sean Lange (6/6/2013)
MID?Yeah, the Q as a whole is a bit meh. I assume he means AVG (to go with the MIN/MAX functions) which I don't see as a very good test (min/max are common use and decrypting the words is more difficult)...if it means median then it's a dodgy question asking for no subquery & in an interview scenario :s
No, I don't mean avg, I mean middle.
Min/max may be common to some, not so common to others; this is not just a basic grouping q, altho it is that also.
The big deal with this question is that your data is limited to 3 values. Do not expand on the possible number of entries (IE: 4/5/100) and a reasonable solution is within grasp.
I remember a conversation about a year ago regarding this and I personally consider it a trick question. I don't build things with expected # of row limiters, I expect requirements to change and I expect to store n rows, not n where n must be 3 or less.
That would be revealing in an interview as well: I don't solve problems I don't like.
My current company works for clients. We push back as much as we can, but at times we have to give in to something they want.
I agree with giving the client a solution. I don't agree that it's required to enforce the limiter and have to recode when performing a more robust solution takes about as much mental resources and allows for expansion.
It's not solving a problem I don't like, I personally feel the rules are twisted into an invalid context and that you have to rely on business restrictions. It can be coded for both their current usage as well as exceptions later, but not with the artificial restriction of no subqueries.
What "expansion"??? This is an interview q only to test thinking ability, like "How many manhole covers are there in NYC?" and other MS classics.
At one point in its development, MySQL didn't allow subqueries, so this actually could have been a real-life q tho.
Besides, all sorts of fixed restrictions exist.
There are always exactly: 24 hours in a day, 7 days in a week and 12 months in a year ... whether we like it or not. Therefore, I don't need to weekly code to be flexible enough to handle an 8-day week: that's just a waste of development time.
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 4:12 pm
ScottPletcher (6/6/2013)
What "expansion"??? This is an interview q only to test thinking ability, like "How many manhole covers are there in NYC?" and other MS classics.At one point in its development, MySQL didn't allow subqueries, so this actually could have been a real-life q tho.
Besides, all sorts of fixed restrictions exist.
There are always exactly: 24 hours in a day, 7 days in a week and 12 months in a year ... whether we like it or not. Therefore, I don't need to weekly code to be flexible enough to handle an 8-day week: that's just a waste of development time.
Trimming down the quote wall we were generating.
The MS question was "why are manhole covers round?", to my knowledge, but that's a different story. Hadn't heard about the count one.
Fixed restrictions are a different story, but really you're talking about containers there. If the restriction was fixed you could turn them into attributes, but we typically don't... but I'm digressing.
Your specific explanation there indicates containers for an 'n' rowset: days. Days fall into the containers you speak of, I agree. That doesn't limit the number of days associated to a separate entity, only how many are within that container for organization. Most customers have 'n' items, but only drag one particular shopping cart around the store... so you could limit it to '1' cart per person, right up until I arrive and I'm shopping for bulk stuff like toilet paper.
If anything, your solution (I'm purposely not posting it) actually takes more thought than the more robust solution which would locate the MID row via ROW_NUMBER() (with expected variance on even values). Now, is it as optimal compared to the non-subquery solution? Hell no. It's multi-pass. If that was a signficant consideration I could see having to re-wire to the business restriction and lose the robust abilities to avoid future recodes.
Also, to your comment about MySQL and subqueries, I thought we were discussing a SQL Server interview question. I could pull out a few wonky things Dataease or Access does, but I wouldn't bring them into an interview discussion about SQL Server as arbitrary coding rules.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 6, 2013 at 4:19 pm
There are always exactly: 24 hours in a day, 7 days in a week and 12 months in a year ... whether we like it or not. Therefore, I don't need to weekly code to be flexible enough to handle an 8-day week: that's just a waste of development time.
Who's to say it won't happen again? If it is up to any customer of mine, there is no such thing as a an absolute requirement...
http://www.cslib.org/CalendarChange.htm
December 31, 1750 was followed by January 1, 1750 (under the "Old Style" calendar, December was the 10th month and January the 11th)
March 24, 1750 was followed by March 25, 1751 (March 25 was the first day of the "Old Style" year)
December 31, 1751 was followed by January 1, 1752 (the switch from March 25 to January 1 as the first day of the year)
September 2, 1752 was followed by September 14, 1752 (drop of 11 days to conform to the Gregorian calendar)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 6, 2013 at 4:52 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).
These are Mid to Sr. Web Developers that also spend about 30% of their time writing T-SQL. To make their lives easier (and mine), I do 100% code reviews which turn out to be mentoring sessions. Sometimes I'll show them some clues as to a better way and let them come up with their own solution or we'll do a rewrite on the spot together. Of course, after about 6 months doing this with any of them, they can generally run circles around the "average" Web Dev when it comes to SQL.
The reason why I don't specify "without a loop" is, as I said in a previous post of mine, getting what I want as the correct answer isn't the goal of the question. The test is used as a "do I even want to talk to this person in an interview" and generally replaces the phone interview. Then, I also use the test to find out how well the developer responds to the "helpful hand". I almost prefer they not know how to do it in a set based fashion so that I can see if they get it when I ask them, "Did you know that this can be done without a loop?" and the conversation continues from there.
The reason why I have them handwrite it is because it takes them out of their comfort zone and I want to see how they are in a zone they normally don't work in. Anyone can debug code if they hit {f5} enough times. I want to know if the "think" SQL and see how they react to the discussion about their code in the interview.
Heh... and if someone thinks you have to write 9 UNION ALLs for the FizzBuzz problem, they probably need to study just a whole lot more. 🙂 Anyone that needs Google for the simple task of solving this problem likely isn't the person I'm looking for. Besides, the person who writes the loop solution probably doesn't know there's a better solution to look for to begin with and that's ok until after I train them.
As for being "harsh", not even. Like I said, I don't actually expect anyone to write the solution that I think is 100% correct never mind doing it in a set based manner. None of the 20 I've interviewed in the last 18 months have come up with a set based solution but I've hired 3 good ones based on how they answered that question (and the other 4) and how they responded in a discussion of the questions in the interview.
So far as someone taking the safe way of using a loop to get it right instead of possibly getting it wrong with set based code goes, I'd much rather see someone that has the nads to try the setbased method even if they can't remember exactly the right way to do it. Like I said, the test isn't about getting things correct... it's about getting things "right".
As for why the questions aren't more difficult, someone in an interview is already nervous enough especially if they really need the job. My goal with the questions isn't to find out how smart I am. It's to find out how much they know. The questions don't need to be difficult for me to figure out if they going to be worth inteviewing or not.
For example, the first couple/three questions in the technical interview start out with stupid simple things like "How do you get the current date and time using T-SQL?" That relaxes folks so they can start thinking instead of being scared. Of course, most people would think that "GETDATE()" would be an acceptable answer. It is but the folks that I ended up hiring answered that question with "to what resolution do you want the time element to be" and then explained the various methods to get the current date and time.
As you can see, my "simple" questions actually do tell me a whole lot about your skills and your attitude.
If I were hiring a Sr. Database Administrator (as I have in the past), the interview questions would start out pretty much the same way. If I felt that I had a good one, then the Sr. Database Adminstrator tough questions would come out. Even at that level, though, I only need an hour or so to find out if the person can do the job or not and whether or not they're going to fit in with the rest of the group.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2013 at 12:55 am
Jeff Moden (6/6/2013)
These are Mid to Sr. Web Developers that also spend about 30% of their time writing T-SQL.As for why the questions aren't more difficult, someone in an interview is already nervous enough especially if they really need the job.
For example, the first couple/three questions in the technical interview start out with stupid simple things like "How do you get the current date and time using T-SQL?" That relaxes folks so they can start thinking instead of being scared. Of course, most people would think that "GETDATE()" would be an acceptable answer. It is but the folks that I ended up hiring answered that question with "to what resolution do you want the time element to be" and then explained the various methods to get the current date and time.
Then they're journeymen who are senior only in age if they don't know what modulus is (and fizzbuzz, old & played out) since most web devs would/should have used it at least once for alternating row styles; Google has limited it's value drastically. Even now, I could go for an interview with you & give a set-based solution with perfect syntax and you'd be like "oh sh*t, we've found the holy grail!"...I'd probably get caught out quickly after that though =( Saying that, many recruiters base senior on a minimum number of years that only journeymen can apply for.
The other people may have known about other date/time options too; just too nervous to mention it as you said~ Did you ask them of the different kinds? They may have known more than the others with the gift of the gab.
June 7, 2013 at 5:31 am
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 <snip>.
Actually, this is an excellent for spotting the "I've proudly been repeating the same week of experience for the past X years now, all hail me" type of "middle/Senior" practitioner. If someone is still writing garbage for simple stuff like this, you know they're not to be trusted with your systems because they're too arrogant and lazy to learn.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply