October 30, 2012 at 5:13 am
Lokesh Vij (10/30/2012)
USERID USERNAME SALES------------------------------
5 XRay NULL
6 Easy 100
1 Joe 400
3 Charlie 700
2 Baker 800
4 Able 1000
Given the results of the query as stated in the question, courtesy of Lokesh, "Easy does have a Sales value"!
Yes, an argument can be made to interpret the question as the author intended - but that's not what the question asked!
Hence the model answer is incorrect.
October 30, 2012 at 5:22 am
An interesting question spoilt by the 3 points of ambiguity.
Salesgoal mistake
The Sales value for easy becomes 100, it is xray that is missing.
It doesn't state that the field is nullable
October 30, 2012 at 5:38 am
L' Eomot Inversé (10/30/2012)
Nice to see a question about a new feature in sql 2012.I took a guess as to what the code was intended to be, and got it right. But there are ambiguities here which we have to resolve by pure guesswork, and maybe the low success rate is the result of this. The current success rate is 23%, and the expected result of chosing one at random from each mutually contradictory pair is 25%, which indicates either no knowledge at all in the respondents (which I don't believe) or enough ambiguity in the question to have much the same effect as if answers had been random.
It would be interesting to find out what percentage got the answer right according the stated question.
Curently 57% selected option 2, but it's not clear how many of those also selected option 3. The upper bound could be calculated as the percentage that selected option 3 - the percentage that got the answer "offcially correct". I.e. 58 - 23 = 35%.
Of course there are those who figured out the intent of the question and answered "correctly". So it's quite possible that as many as 50% did undertsand the issues the question was supposed to test.
October 30, 2012 at 5:56 am
I think the question is not ready to be published. Description is not clear and have many mistakes like mentioned term "SalesGoal" but I did not find it anywhere 😀
Next is
> Sales value for User Easy does NOT appear in the results
According to the task' explanation that sounds like
"SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) sales FROM #Users1 ORDER BY SALES"
field sales for user "Easy" will have value = 100. It is obvious and I've got this result.
I've got a question why do choice "Sales value for User Easy does NOT appear in the results," considered as correct part of answer ?!
I would like to get point back.....
MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2
October 30, 2012 at 6:04 am
ben.norris (10/30/2012)
It doesn't state that the field is nullable
First: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.
Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.
October 30, 2012 at 6:09 am
Konstantin Reu (10/30/2012)
I think the question is not ready to be published. Description is not clear and have many mistakes like mentioned term "SalesGoal" but I did not find it anywhere 😀Next is
> Sales value for User Easy does NOT appear in the results
According to the task' explanation that sounds like
"SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) sales FROM #Users1 ORDER BY SALES"
field sales for user "Easy" will have value = 100. It is obvious and I've got this result.
I've got a question why do choice "Sales value for User Easy does NOT appear in the results," considered as correct part of answer ?!
I would like to get point back.....
The sales value for User Easy is 50. 50 is not returned in the resultset.
Hence, the sales value for user easy does not appear in the results.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 30, 2012 at 6:13 am
Hugo Kornelis (10/30/2012)
ben.norris (10/30/2012)
It doesn't state that the field is nullableFirst: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.
Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.
According to the docs LEAD returns the default value which only happens to be NULL for nullable columns. So yes it does change the answer. A poor question to rely on assumption for something you are specifically looking for in the answer.
October 30, 2012 at 6:24 am
ben.norris (10/30/2012)
Hugo Kornelis (10/30/2012)
ben.norris (10/30/2012)
It doesn't state that the field is nullableFirst: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.
Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.
According to the docs LEAD returns the default value which only happens to be NULL for nullable columns. So yes it does change the answer. A poor question to rely on assumption for something you are specifically looking for in the answer.
default is the optional 3rd argument to the LEAD function. This is described as: "The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. ..."
In the question, no default is specified, hence even if SALES were non nullable, the LEAD function would return NULL.
October 30, 2012 at 6:31 am
craig 81366 (10/30/2012)
ben.norris (10/30/2012)
Hugo Kornelis (10/30/2012)
ben.norris (10/30/2012)
It doesn't state that the field is nullableFirst: It does. The CREATE TABLE script does not include a NOT NULL constraint, so the columns are all nullable.
Second: That is irrelevant. Even when the column is not nullable, operating a LEAD function on it can still result in NULL values. So every column in a result set that is calculated using LEAD will always be nullable, and that does not have to be explicitly stated.
According to the docs LEAD returns the default value which only happens to be NULL for nullable columns. So yes it does change the answer. A poor question to rely on assumption for something you are specifically looking for in the answer.
default is the optional 3rd argument to the LEAD function. This is described as: "The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. ..."
In the question, no default is specified, hence even if SALES were non nullable, the LEAD function would return NULL.
Aha, right you are, my mistake. Shame Hugo was too busy being condescending to make that point clear.
October 30, 2012 at 8:42 am
Koen Verbeeck (10/30/2012)
Konstantin Reu (10/30/2012)
I think the question is not ready to be published. Description is not clear and have many mistakes like mentioned term "SalesGoal" but I did not find it anywhere 😀Next is
> Sales value for User Easy does NOT appear in the results
According to the task' explanation that sounds like
"SELECT UserId,UserName,LEAD (Sales,1) OVER (ORDER BY Sales) sales FROM #Users1 ORDER BY SALES"
field sales for user "Easy" will have value = 100. It is obvious and I've got this result.
I've got a question why do choice "Sales value for User Easy does NOT appear in the results," considered as correct part of answer ?!
I would like to get point back.....
The sales value for User Easy is 50. 50 is not returned in the resultset.
Hence, the sales value for user easy does not appear in the results.
The column in the results which has been given the name "Sales" contains a value for user Easy. Hence the "Sales value for user Easy" DOES appear in the results.
And by what process other than guesswork, does "the sales recorded for a completely different person" translate into "salesgoal" ?
October 30, 2012 at 8:59 am
ben.norris (10/30/2012)
Aha, right you are, my mistake. Shame Hugo was too busy being condescending to make that point clear.
I am sorry to hear that you perceived my reply as condescending. That was never my intention.
October 30, 2012 at 9:01 am
vk-kirov (10/30/2012)
...The value 50.00 does not appear in the results, so I see no problem with the answer given....
Just trying to understand...
As it says OVER (ORDER BY Sales) and EASY is on the low it will never pick the 50.00 of him as the OFFSET is been used as 1 so it has to go to the next available row in the dataset and it has to pick the value. If 50.00 was supposed to be displayed for EASY then does it not defeats the purpose of using the LEAD?
If the OVER (Order By UserID) was used then XRAY will get the 50.00 as he is on the 5 position and EASY in on 6th so the 1000 will be replaced by the LEAD value to 50.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
October 30, 2012 at 10:11 am
Raghavendra Mudugal (10/30/2012)
vk-kirov (10/30/2012)
...The value 50.00 does not appear in the results, so I see no problem with the answer given....Just trying to understand...
As it says OVER (ORDER BY Sales) and EASY is on the low it will never pick the 50.00 of him as the OFFSET is been used as 1 so it has to go to the next available row in the dataset and it has to pick the value. If 50.00 was supposed to be displayed for EASY then does it not defeats the purpose of using the LEAD?
If the OVER (Order By UserID) was used then XRAY will get the 50.00 as he is on the 5 position and EASY in on 6th so the 1000 will be replaced by the LEAD value to 50.
Ah! Someone who wants to understand and not just complain.... Let's step through the query, and I'll admit that I may not be the best to do that since, as I said in the second comment posted, I don't have SQL 2012 installed. First, for ease of review, here's the CREATE TABLE:
CREATE TABLE #Users1(UserId INT IDENTITY,UserName VARCHAR(8),Sales Decimal(6,2))
and its stated contents:
UserId UserName Sales
1 Joe 100.00
2 Baker 700.00
3 Charlie 400.00
4 Able 800.00
5 XRay 1000.00
6 Easy 50.00
and the query (reformatted a bit for readability):
SELECT UserId--1
,UserName--2
,LEAD (Sales,1)--3
OVER (ORDER BY Sales)--4
AS sales--5
FROM #Users1--6
ORDER BY SALES--7
The LEAD() function in line 3 above may take three immediate parameters, two of which are used here. The first says which column in which to look ahead (SALES), and the second says how far (1 row). The third parameter would say what to return as a default if no row is available for the look-ahead. Here it's left off, so is NULL. In addition to the three parameters, the LEAD() function also has other elements controlling its behaviour in its OVER clause, elements that looked very familiar to this SQL 2005 user from their similarity to the ranking functions Row_Number(), NTile(), Rank() and Dense_Rank(). The "Order by Sales" in line 4 above tells LEAD to put the rows in that order before reporting what's in the next row. This means that internally for this function, the table is looked at as having a logical order of
UserId UserName Sales
6 Easy 50.00
1 Joe 100.00
3 Charlie 400.00
2 Baker 700.00
4 Able 800.00
5 XRay 1000.00
But of course, that's not what's returned as the query's result. For that, we get what's specified in the Select list -- UserID (line 1), UserName (line 2), and the output of the LEAD() function assigned the alias "sales" (line 5). For the first row considered in the order of the table's "Sales", we get UserID =6, UserName=Easy and the LEAD() function gives the next row's value of Sales, which is 100.00. Thus, the result for "Easy" is:
6 Easy 100.00
Similarly, each results row is given its third column value in turn. The row at the end, based on the "OVER (Order By Sales)", is that of UserID=6, UserName=XRay. Since there's no following row, the LEAD() function gives that row the default of NULL. If the third parameter had been specified in line 3 of the query as, say
LEAD (Sales,1,5000)
then it would have returned that specified default of 5000.
Finally, the result set itself has its own ORDER BY in line 7. Recall that although an alias may not be used in the WHERE clause, it is certainly available for the ORDER BY of a query. So, the results are returned in the order of the alias "sales", that is, the result of the LEAD() function. I would expect the results to be as follows, with UserID 5 at the top since NULL would sort before the numerical values:
UserId UserName Sales
5 XRay NULL
6 Easy 100.00
1 Joe 400.00
3 Charlie 700.00
2 Baker 800.00
4 Able 1000.00
October 30, 2012 at 10:30 am
john.arnott (10/30/2012)
Raghavendra Mudugal (10/30/2012)
Just trying to understand...(...)
If the OVER (Order By UserID) was used then XRAY will get the 50.00 as he is on the 5 position and EASY in on 6th so the 1000 will be replaced by the LEAD value to 50.
Ah! Someone who wants to understand and not just complain....
An excellent explanation, John! Well done!
You did overlook the last part of Raghavendra's question, so I'll take that. The answer is YES. If you change the OVER clause to (ORDER BY UserID), each row will get the sales value of the next user ordered by UserID, so Joe get 700, Baker 400, Charlie 800, Able 1000, XRay 50, and Easy gets the NUL in this case.
October 30, 2012 at 11:08 am
Great question. Missed it, but learned something. Also made me look forward to the upgrade to SQL 2012!
Rob Schripsema
Propack, Inc.
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply