November 30, 2012 at 8:28 am
Jeff Moden (11/29/2012)
That's a good test question, Scott. The only thing I might change is requiring them to do it "in a single query". It's sometimes amazing what kind of performance people can come up with if you remove that "simple" and unnecessary restriction.
But, unfortunately, that makes it too easy. With enough levels of CTEs, it's not that hard to figure out.
I posted it on a forum to get some idea of what to expect from my students, and got some pretty mediocre answers from (supposedly) experienced and skilled T-SQLers.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2012 at 8:42 am
mid_score --may be NULL if only one test taken
low_score --may be NULL if only one or two tests taken
Looks a bit illogical to me...
I can see why mid_score should be NULL when only one test taken, but I would think it also should be NULL in case of two tests...
But, it should be possible to show non-NULL low_score when two tests are done.
November 30, 2012 at 8:45 am
November 30, 2012 at 8:45 am
Eugene Elutin (11/30/2012)
mid_score --may be NULL if only one test taken
low_score --may be NULL if only one or two tests taken
Looks a bit illogical to me...
I can see why mid_score should be NULL when only one test taken, but I would think it also should be NULL in case of two tests...
But, it should be possible to show non-NULL low_score when two tests are done.
If there are only two scores they are listed as High and Mid.
It's to be consistent: the scores are filled from left to right.
But of course I'd accept as correct an otherwise valid answer that filled in Hi, Low, Mid order.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2012 at 8:47 am
(Deleted, duplicate)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2012 at 8:49 am
what do you mean by "mid-score"? Do you mean average or second largest score (from three possible ones)?
November 30, 2012 at 8:49 am
Eugene Elutin (11/30/2012)
what do you mean by "mid-score"? Do you mean average or second largest score (from three possible ones)?
The middle one. You're listing all the actual scores each student received on their test(s).
So, Bill took two tests, got 93 and 85.
Sally tooks three tests and got 88, 64 and 99.
Tom took one test and got 87.
Bill 93 85
Sally 99 88 64
Tom 87
Remember: vanilla SQL -- no VALUES clauses, OVER, etc.. You don't need any of that stuff anyway. (This is coding exercise not performance exercise -- those would come much later for a student.)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2012 at 9:06 am
... and got some pretty mediocre answers from (supposedly) experienced and skilled T-SQLers.
I am not be surprised when good skilled T-SQLers come up with stupid answers on paper during interviews. I do prefer take them over to PC with SSMS...
Anyway, I'm happy if you will rate myself here 🙂 :
declare @Test_Scores table ( student_id int, date_taken datetime, score decimal(4, 1))
insert @Test_Scores
select 1,GETDATE(),93
union select 1,GETDATE(),85
union select 2,GETDATE(),88
union select 2,GETDATE(),64
union select 2,GETDATE(),99
union select 3,GETDATE(),87
-- I think that is more logical (and 1 less code line :-))
select student_id
,MAX(score) as high_score
,CASE WHEN COUNT(*) = 3
THEN SUM(score) - MAX(SCORE) - MIN(score)
ELSE NULL
END as mid_score
,CASE WHEN COUNT(*) > 1
THEN MIN(score)
ELSE NULL
END as low_score
from @Test_Scores
group by student_id
-- also possible to fill it "left-to-right" (J.C. might call it 1970's Sybase/Unix/Punch card/sequencial file style :hehe:)
select student_id
,MAX(score) as high_score
,CASE COUNT(*)
WHEN 3 THEN SUM(score) - MAX(SCORE) - MIN(score)
WHEN 2 THEN MIN(score)
ELSE NULL
END as mid_score
,CASE COUNT(*)
WHEN 3 THEN MIN(score)
ELSE NULL
END as low_score
from @Test_Scores
group by student_id
November 30, 2012 at 9:13 am
another one, for my logic only, without even CASE WHEN (actually it's hidden under NULLIF):
select student_id
,high_score = MAX(score)
,mid_score = NULLIF(~CAST(COUNT(*) - 3 AS BIT),0) *
(SUM(score) - MAX(SCORE) - MIN(score))
,low_score = NULLIF(CAST(COUNT(*) - 2 AS BIT),0) * MIN(score)
from @Test_Scores
group by student_id
November 30, 2012 at 10:19 am
ScottPletcher (11/30/2012)
Jeff Moden (11/29/2012)
That's a good test question, Scott. The only thing I might change is requiring them to do it "in a single query". It's sometimes amazing what kind of performance people can come up with if you remove that "simple" and unnecessary restriction.But, unfortunately, that makes it too easy. With enough levels of CTEs, it's not that hard to figure out.
I posted it on a forum to get some idea of what to expect from my students, and got some pretty mediocre answers from (supposedly) experienced and skilled T-SQLers.
That's the whole problem though. I would never require someone to do this with "vanilla" code because I don't expect "vanilla" code from someone working for me. I expect accurate and high performance code that makes use of any and all features of the version of SQL Server that I'd have them working with. To wit, doing something all in one query is frequently the wrong way to do something.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2012 at 10:49 am
Jeff Moden (11/30/2012)
ScottPletcher (11/30/2012)
Jeff Moden (11/29/2012)
That's a good test question, Scott. The only thing I might change is requiring them to do it "in a single query". It's sometimes amazing what kind of performance people can come up with if you remove that "simple" and unnecessary restriction.But, unfortunately, that makes it too easy. With enough levels of CTEs, it's not that hard to figure out.
I posted it on a forum to get some idea of what to expect from my students, and got some pretty mediocre answers from (supposedly) experienced and skilled T-SQLers.
That's the whole problem though. I would never require someone to do this with "vanilla" code because I don't expect "vanilla" code from someone working for me. I expect accurate and high performance code that makes use of any and all features of the version of SQL Server that I'd have them working with. To wit, doing something all in one query is frequently the wrong way to do something.
And I wouldn't want to hire someone who was incapable of writing a simple query to satisfy a simple request.
To each his own.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2012 at 10:54 am
Eugene Elutin (11/30/2012)
... and got some pretty mediocre answers from (supposedly) experienced and skilled T-SQLers.
I am not be surprised when good skilled T-SQLers come up with stupid answers on paper during interviews. I do prefer take them over to PC with SSMS...
Anyway, I'm happy if you will rate myself here 🙂 :
declare @Test_Scores table ( student_id int, date_taken datetime, score decimal(4, 1))
insert @Test_Scores
select 1,GETDATE(),93
union select 1,GETDATE(),85
union select 2,GETDATE(),88
union select 2,GETDATE(),64
union select 2,GETDATE(),99
union select 3,GETDATE(),87
-- I think that is more logical (and 1 less code line :-))
select student_id
,MAX(score) as high_score
,CASE WHEN COUNT(*) = 3
THEN SUM(score) - MAX(SCORE) - MIN(score)
ELSE NULL
END as mid_score
,CASE WHEN COUNT(*) > 1
THEN MIN(score)
ELSE NULL
END as low_score
from @Test_Scores
group by student_id
-- also possible to fill it "left-to-right" (J.C. might call it 1970's Sybase/Unix/Punch card/sequencial file style :hehe:)
select student_id
,MAX(score) as high_score
,CASE COUNT(*)
WHEN 3 THEN SUM(score) - MAX(SCORE) - MIN(score)
WHEN 2 THEN MIN(score)
ELSE NULL
END as mid_score
,CASE COUNT(*)
WHEN 3 THEN MIN(score)
ELSE NULL
END as low_score
from @Test_Scores
group by student_id
That is excellent!
In the interests of being thorough, I added a Message to my proposed solution back to them:
SELECT
...as above...,
CASE WHEN COUNT(*) > 3 THEN 'ERROR: > 3 TESTS FOUND, MID SCORE INACCURATE!' ELSE '' END AS Message
To me, this teaches the students something important as well:
Just because someone promises you there are only 3 values -- pinkie swear, cross their heart -- don't necessarily believe them! Either verify it 100%, or, if it's easy, add something to catch the unexpected. My sample table of data would of course have one student_id that had 4 test results and a middle value of 105 🙂 .
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2012 at 3:28 pm
ScottPletcher (11/30/2012)
...In the interests of being thorough, I added a Message to my proposed solution back to them:
SELECT
...as above...,
CASE WHEN COUNT(*) > 3 THEN 'ERROR: > 3 TESTS FOUND, MID SCORE INACCURATE!' ELSE '' END AS Message
To me, this teaches the students something important as well:
Just because someone promises you there are only 3 values -- pinkie swear, cross their heart -- don't necessarily believe them! Either verify it 100%, or, if it's easy, add something to catch the unexpected. My sample table of data would of course have one student_id that had 4 test results and a middle value of 105 🙂 .
That is I guess quintessence of the difference between "school" and "practice".
Because, I would most definitely not add this check in my code!
If someone would tell me that table should allow "only 3 values", instead of worrying about possibility of having data inconsistent at every query, I would enforce it by trigger or a constraint on computed column...
Also, I kind of agree with Jeff on restrictions such as "single query" and "using basic sql". I would really want to see the best possible solution with showing knowledge of the most advanced SQL features instead of ability to solve irrelevant puzzles (aka SELECT EXP($) ;-)).
November 30, 2012 at 4:13 pm
Eugene Elutin (11/30/2012)
ScottPletcher (11/30/2012)
...In the interests of being thorough, I added a Message to my proposed solution back to them:
SELECT
...as above...,
CASE WHEN COUNT(*) > 3 THEN 'ERROR: > 3 TESTS FOUND, MID SCORE INACCURATE!' ELSE '' END AS Message
To me, this teaches the students something important as well:
Just because someone promises you there are only 3 values -- pinkie swear, cross their heart -- don't necessarily believe them! Either verify it 100%, or, if it's easy, add something to catch the unexpected. My sample table of data would of course have one student_id that had 4 test results and a middle value of 105 🙂 .
That is I guess quintessence of the difference between "school" and "practice".
Because, I would most definitely not add this check in my code!
If someone would tell me that table should allow "only 3 values", instead of worrying about possibility of having data inconsistent at every query, I would enforce it by trigger or a constraint on computed column...
Also, I kind of agree with Jeff on restrictions such as "single query" and "using basic sql". I would really want to see the best possible solution with showing knowledge of the most advanced SQL features instead of ability to solve irrelevant puzzles (aka SELECT EXP($) ;-)).
If they can't solve this query without three CTEs and two table joins, that's not irrelevant to me. What better solution do you propose here with more advanced SQL?
If they refuse to use only basic SQL for a query when I ask them to, that's not irrelevant to me either: it tells me things (good and bad).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2012 at 4:39 pm
ScottPletcher (11/30/2012)
Eugene Elutin (11/30/2012)
ScottPletcher (11/30/2012)
...In the interests of being thorough, I added a Message to my proposed solution back to them:
SELECT
...as above...,
CASE WHEN COUNT(*) > 3 THEN 'ERROR: > 3 TESTS FOUND, MID SCORE INACCURATE!' ELSE '' END AS Message
To me, this teaches the students something important as well:
Just because someone promises you there are only 3 values -- pinkie swear, cross their heart -- don't necessarily believe them! Either verify it 100%, or, if it's easy, add something to catch the unexpected. My sample table of data would of course have one student_id that had 4 test results and a middle value of 105 🙂 .
That is I guess quintessence of the difference between "school" and "practice".
Because, I would most definitely not add this check in my code!
If someone would tell me that table should allow "only 3 values", instead of worrying about possibility of having data inconsistent at every query, I would enforce it by trigger or a constraint on computed column...
Also, I kind of agree with Jeff on restrictions such as "single query" and "using basic sql". I would really want to see the best possible solution with showing knowledge of the most advanced SQL features instead of ability to solve irrelevant puzzles (aka SELECT EXP($) ;-)).
If they can't solve this query without three CTEs and two table joins, that's not irrelevant to me. What better solution do you propose here with more advanced SQL?
If they refuse to use only basic SQL for a query when I ask them to, that's not irrelevant to me either: it tells me things (good and bad).
This will work even if you throw in a curve of 4 scores it will take the 3 earliest scores of course it uses some advanced sql (ROW_NUMBER() and PIVOT)
declare @Test_Scores table ( student_id int, date_taken datetime, score decimal(4, 1))
insert @Test_Scores
select 1,GETDATE(),93
union select 1,GETDATE() + 1,85
union select 2,GETDATE(),88
union select 2,GETDATE() + 1,64
union select 2,GETDATE() + 2,99
union select 3,GETDATE(),87
SELECT student_id, [1],[2],[3]
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY date_taken) rn, student_id, score
FROM @Test_Scores)p
PIVOT
(MAX(Score) FOR rn IN ([1],[2],[3]))pvt
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 31 through 45 (of 73 total)
You must be logged in to reply to this topic. Login to reply