21
\$\begingroup\$

In the pursuit of SQL enlightenment, I wrote a quick SEDE query to display Rep, Age and Overall Rank.
Sorted by Age, then Rank (Which is generated by Rep).

SELECT TOP 150 
ROW_NUMBER() OVER (ORDER BY Users.Reputation DESC) AS Rank,
  Users.Id as [User Link], 
  Users.Age,
  Users.Reputation as Rep
FROM Users
WHERE Users.Age IS NOT NULL AND Users.Reputation > 150 AND Users.Age <= 30
ORDER BY Users.Age ASC, Rank ASC;

I attempted formatting, but it feels wrong, and my (possibly) overly long WHERE statement seems incorrectly formatted as well.

What is a more optimal way to write this, if one does exist?

I've had parameters mentioned as an idea on improvement (age range and selection limitation).

\$\endgroup\$
  • 3
    \$\begingroup\$ I was surprised that I was not in the list, but then I see you're selecting the top 150 and ordering by age first. \$\endgroup\$ – Marc-Andre Jun 11 '15 at 14:06
  • 13
    \$\begingroup\$ I was surprised I was not on that list, but then I realized.... Get off my lawn!!!! ... kids these days.... :-] \$\endgroup\$ – rolfl Jun 11 '15 at 14:34
  • 1
    \$\begingroup\$ You finding this site at age of 16 is going to help you a lot in the future (I wish I participated when the site became beta). Well Done. \$\endgroup\$ – bhathiya-perera Jun 13 '15 at 3:41
  • \$\begingroup\$ You do realize a lot of users don't have their birthday set-up, right? \$\endgroup\$ – Mast Aug 27 '15 at 13:02
  • \$\begingroup\$ @Mast, yes, if a user chooses to set it up, then they get marked. It's don't a definite measure, it has limitations to which, I cannot work around. \$\endgroup\$ – Quill Aug 27 '15 at 13:36
16
\$\begingroup\$

A few things, some nitpicks, some UX, some probably-bugs. What Lyle's Mug has already stated is also part of my answer, but first things first:

UX

This query is quite unwieldy to use, because all the things you might want to play with are hardcoded.
SEDE allows using parameters, with a rather simple syntax:

##name:type[?if optional, default value]##

replacing all your magic numbers gets us to:

SELECT TOP ##x:int?150##
 -- ...
WHERE User.Age IS NOT NULL AND Users.Reputation > ##minrep:int?150## AND User.Age <= ##age:int?30##

Nitpicks:

Note that this already stretches out that single-line WHERE quite much. I really like queries to state WHERE conditions on separate lines, because that makes it easier to process them.

Additionally I strongly recommend a lightly different comma-placement when listing columns, namely the comma before the declaration.
This has the advantage of allowing removals without running into syntax errors each time.

Probably-Bugs:

  • The ordering you Limit By is ... skewed? strange? ... well you select the youngest users and limit according to age, when the purpose of such a query is most probably reputation based stats.

  • Off-By-One: WHERE Rep > 150 excludes users with 150 rep!
    Then again it seems that the where clause about rep is moot anyways...
    If you remove it, there's a way to cut away large parts of "inactive" userbase depending on reputation. Usually when querying that table, I explicitly exclude users with 1 and 101 rep. These two values are a strong indicator for non-activity.

My final version:

SELECT TOP ##x:int?150##
  ROW_NUMBER() OVER (ORDER BY Users.Reputation DESC) AS Rank
  , Users.Id as [User Link]
  , Users.Age
  , Users.Reputation as Rep
FROM Users
WHERE Users.Age <= ##age:int?30##
ORDER BY Rank ASC;

(also available on SEDE)

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ What about "Ties"? Might not be an issue with "highest" ranks (Odds of rank #150 and #151 having the same rank?), but say with Lowest Ranking Youngsters? (As of this post, 2 people have 100 rep.. and 2610 have 101 rep... would make getting the "Top 5 Lowest" a "crapshoot") \$\endgroup\$ – WernerCD Jun 11 '15 at 19:09
  • \$\begingroup\$ Rank is unique, under the assumption there are no reputation ties. to break ties, you'd have to order by an additional colum in the OVER definition \$\endgroup\$ – Vogel612 Jun 11 '15 at 19:12
  • \$\begingroup\$ Or, as in the posted varation of your answer, use with ties :) Just learned about it myself poking around. I would just add that the "ties" could be a bug, query depending - most likely not this case, but there is a chance. \$\endgroup\$ – WernerCD Jun 11 '15 at 19:12
  • \$\begingroup\$ @WernerCD make that an answer of your own ;) \$\endgroup\$ – Vogel612 Jun 11 '15 at 19:20
  • 1
    \$\begingroup\$ I didn't think it needed a full answer, since it's just adding a caveat to your very complete answer - about ties and reversing your query to show how a "Select top 5" turns into "Select top 2000" because of said ties :) \$\endgroup\$ – WernerCD Jun 11 '15 at 19:29
13
\$\begingroup\$

If you write the query where you first order by rank and then by age, you will get a better result, and you should be able to get rid of part of the where statement, which I would think would make the query a little faster.

SELECT TOP 150 
ROW_NUMBER() OVER (ORDER BY Users.Reputation DESC) AS Rank,
  Users.Id as [User Link], 
  Users.Age,
  Users.Reputation as Rep
FROM Users
WHERE Users.Age <= 30
ORDER BY Rank ASC, Users.Age ASC

I was able to get rid of two Where Statements from this query doing it this way.

top users under 30

You do not get the same results, but you get expected results. when I saw this your question and query, and then ran your query I was surprised, as most users were, but the query I have gives results that you would expect to see when you run a query with the description "Top 150 users under age 30"

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ the results here may not match up... \$\endgroup\$ – Vogel612 Jun 11 '15 at 14:14
  • 2
    \$\begingroup\$ That's funny, my age is wrong in that list... Was really worried that I had gone completely mad, luckily Facebook saved the day. \$\endgroup\$ – RobH Jun 11 '15 at 14:28
10
\$\begingroup\$

It's not obvious what this query does without reading the code. You should add a title and description in Stack Exchange Data Explorer. I also find the columns illogically arranged: I would expect

  1. Age (listed first because that is your primary sort key)
  2. User Link (to see whom we are talking about)
  3. Rep (more information about the user who has just been identified)
  4. Rep Rank (additional information about the reputation score)

    "Rank" by itself it too ambiguous, as it does not convey what you are ranking the users by.

If you want to rank users by reputation, use RANK(), which handles ties nicely, instead of ROW_NUMBER(), which breaks ties arbitrarily.

Since there is just one table involved in this query, it would be more readable if you did not to qualify all the column names with Users..

You don't need to check for Age IS NOT NULL, since only non-null values will satisfy Age <= 30.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.