I want back my Points

Question

I want back my Points

Carlo Romagnano

SSC-Insane

Points: 22680
More actions
April 6, 2017 at 12:05 am

#320223

Comments posted to this topic are about the item I want back my Points

Viewing 15 posts - 1 through 15 (of 21 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

Nice question. Did make me wonder for second, due to the DISTINCT, but then the brain (coffee) kicked it. I'm impressed that someone chose 5,5,5, even with your nice "(Please don't choose this one)" statement 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

call.copse SSCoach Points: 18064 More actions · Answer 2

Thom A - Thursday, April 6, 2017 2:23 AM
Nice question. Did make me wonder for second, due to the DISTINCT, but then the brain (coffee) kicked it. I'm impressed that someone chose 5,5,5, even with your nice "(Please don't choose this one)" statement 🙂

I shook my head slightly seeing that I must admit. I guess it could have been a double bluff! Even so...

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 3

Great question, thanks Carlo
The DISTINCT with the MIN & MAX was a clever red herring .

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

GaryV Hall of Fame Points: 3307 More actions · Answer 4

An interesting question, but it could have been more interesting.
You could have added more values, so that the number of 3's and 5's was not equal - and the answer is still the same.
And the answer is the same, with or without DISTINCT.
And that applies, even if multiple more 5's are added to the values, because the values are all integers and the average comes out as a integer, truncating the decimal part.

George Vobr SSChampion Points: 10007 More actions · Answer 5

Thanks Carlo, I appreciate the brief but concise remarks to links in the explanation. 🙂

webrunner SSC-Dedicated Points: 31879 More actions · Answer 6

Thanks for the question. I was relieved to find that the answer was not one of those counter-intuitive ones lol.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 7

gvoshol 73146 - Thursday, April 6, 2017 5:50 AM
An interesting question, but it could have been more interesting.
You could have added more values, so that the number of 3's and 5's was not equal - and the answer is still the same.
And the answer is the same, with or without DISTINCT.
And that applies, even if multiple more 5's are added to the values, because the values are all integers and the average comes out as a integer, truncating the decimal part.

I agree.... having an extra 3 or 5 (on or the other) in the values given would have really tested someone's knowledge of NULLs, Aggregates, Integer Math, and Distinct. Still, great question especially since such a seemingly simple question requires a good bit of the knowledge of the basics. Hopefully, the people that make it a habit of just running the code to get the answer actually go back and figure out the WHY and the HOW. Overall, it's a great question. I'm going to add a COUNT to it and use it as the interview question I ask right after the one about how to get the current date and time. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99133 More actions · Answer 8

Jeff Moden - Thursday, April 6, 2017 8:04 AM
right after the one about how to get the current date and time. 😉

Out of interest, how many people fail that one? I feel like I'm going to be... Unpleasantly unsurprised?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Bill SSC Enthusiast Points: 194 More actions · Answer 9

Good question. I missed the effect of DISTINCT in the AVG aggregation, but as previously stated, the answer didn't change for the values provided. A better challenge would be to use the values:
7,7,3,3,3,3,null
In this case, DISTINCT does affect the results.

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 10

Since we all did so well yesterday and today, I think we deserve a treat - like an extra point.
(:

GaryV Hall of Fame Points: 3307 More actions · Answer 11

leddybill - Thursday, April 6, 2017 8:25 AM
Good question. I missed the effect of DISTINCT in the AVG aggregation, but as previously stated, the answer didn't change for the values provided. A better challenge would be to use the values:
7,7,3,3,3,3,null
In this case, DISTINCT does affect the results.

It doesn't make a difference for me running it on SS2012, because it's doing integer arithmetic. Is there a different result on other versions?

Change one of the values to 3.0, and then distinct does matter.

Bill SSC Enthusiast Points: 194 More actions · Answer 12

gvoshol 73146 - Thursday, April 6, 2017 9:10 AM
leddybill - Thursday, April 6, 2017 8:25 AM
Good question. I missed the effect of DISTINCT in the AVG aggregation, but as previously stated, the answer didn't change for the values provided. A better challenge would be to use the values:
7,7,3,3,3,3,null
In this case, DISTINCT does affect the results.
It doesn't make a difference for me running it on SS2012, because it's doing integer arithmetic. Is there a different result on other versions?
Change one of the values to 3.0, and then distinct does matter.

Hi gvoshol73146. Using SS2012, I still see a difference caused by DISTINCT:
SELECT MAX(DISTINCT [Points]) ,MIN(DISTINCT [Points]) ,AVG(DISTINCT [Points]) ,AVG( [Points]) FROM (VALUES (7) ,(7) ,(3) ,(3) ,(3) ,(3) ,(NULL) ) AS V([Points])
This returns 7,3,5,4 and illustrates the how DISTINCT affects the calcs. Change the first value to 7.0, and you will get the floating point calculation resulting in 7.0,3.0,5.00000,4.3333

GaryV Hall of Fame Points: 3307 More actions · Answer 13

leddybill - Thursday, April 6, 2017 9:33 AM
gvoshol 73146 - Thursday, April 6, 2017 9:10 AM
leddybill - Thursday, April 6, 2017 8:25 AM
Good question. I missed the effect of DISTINCT in the AVG aggregation, but as previously stated, the answer didn't change for the values provided. A better challenge would be to use the values:
7,7,3,3,3,3,null
In this case, DISTINCT does affect the results.
It doesn't make a difference for me running it on SS2012, because it's doing integer arithmetic. Is there a different result on other versions?
Change one of the values to 3.0, and then distinct does matter.
Hi gvoshol73146. Using SS2012, I still see a difference caused by DISTINCT:
SELECT MAX(DISTINCT [Points]) ,MIN(DISTINCT [Points]) ,AVG(DISTINCT [Points]) ,AVG( [Points]) FROM (VALUES (7) ,(7) ,(3) ,(3) ,(3) ,(3) ,(NULL) ) AS V([Points])
This returns 7,3,5,4 and illustrates the how DISTINCT affects the calcs. Change the first value to 7.0, and you will get the floating point calculation resulting in 7.0,3.0,5.00000,4.3333

You are correct. It is data dependent.

Using 3's and 5's, the average comes out to 4, DISTINCT or not. That's because the average is 4.xxx which comes out to 4 integer. Put in enough 3's, though, and it would come out as 3.xxx and report as a 3.

Change to some 7's, and the average is sometimes 5.xxx and sometimes 4.xxx, depending on how many of the numbers we use. (Use enough 7's, and it would be 6.xxx)

I didn't "test" enough data cases! - Still, it shows the problems that can crop up if you aren't paying attention to integer arithmetic.

Revenant SSC-Forever Points: 42467 More actions · Answer 14

Revenant

SSC-Forever

Points: 42467

April 6, 2017 at 9:59 am

#1937081

Really, really simple. Thanks, Carlo!