Top users under age 30, sorted by age, with rank

Question

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).

Answer 1

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)

Answer 2

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"

Answer 3

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.