SEDE-Query for Rep-Trivia

Question

The code should be relatively self-explanatory. I was wondering how much rep the Top Voters on sites did "generate" on a site, in comparison to how much rep they "own".

Therefore I created a little SEDE-Query and I am now wondering, if I follow good SQL-Practices and how the performance of the query could possibly be improved (even though it's rather simple already..)

WITH Voters AS(
   SELECT DISTINCT UpVotes, DownVotes, Id, Reputation
   FROM Users
   WHERE Reputation != 101 AND Reputation != 1 --Exclude users without repchanges
   )

SELECT TOP ##topX:int?20##
    Users.Id as [UserLink],
    Users.DisplayName as [SortName],
    Voters.Upvotes * 8 as [EstimatedAddedRep], 
    --as there is no split between question and answer votes
    -- we take 8 as median in accordance to the tendency that there are more
    -- answer votes (10 rep) than question votes (5 rep)
    Voters.Downvotes * 3 as [EstimatedDestroyedRep],
    Users.Reputation as [OwnedRep],
    Voters.Upvotes * 8 / Users.Reputation as [Ratio]
FROM Users, Voters
WHERE users.id = Voters.Id
GROUP BY Users.Id, Users.DisplayName, Users.Reputation, Voters.UpVotes, Voters.DownVotes
ORDER BY EstimatedAddedRep DESC

For anyone who wants to run the newest query, you can do so here: Rep "Created" vs Rep "owned" by topX voters

Answer 1

First of all, when you are selecting and want to perform mathematics on it you should directly specify what you want done

instead of

Voters.Upvotes * 8 as [EstimatedAddedRep], 

it should be

(Voters.Upvotes * 8) AS [EstimatedAddedRep]     

And

Voters.Upvotes * 8 / Users.Reputation as [Ratio]

Should be

((Voters.Upvotes * 8) / (Users.Reputation)) as [Ratio]

you should always specify with parenthesis when performing arithmetic in code, because it doesn't always do what you think it will do.

---

I don't do this with my FROM and WHERE Statements

FROM Users, Voters
WHERE users.id = Voters.Id

I would do this

FROM Users INNER JOIN Voters ON Users.Id = Voters.Id

and leave out the WHERE clause.

---

For the issue brought up in Chat you would just need to add it in like this (simple query so not an issue to repeat yourself in the select)

((Voters.Upvotes * 8) / Users.Reputation) AS RepCreated --?????

---

Check all your Columns and Tables as well, you have some weird casing issues where sometimes you capitalize and sometimes you don't, it was hard to figure out what was right

Answer 2

Although it makes little (almost no) difference to the SQL engine, it might be more friendly to the user to produce the resulting ratio as a floating point number instead of an integer (probably rounded to only one or two places after the decimal point).

This is particularly true for any user whose rep is (even slightly) higher than the amount it estimates they've created. Showing all such users as having a ratio of 0 basically leaves it to them to do the math in their head to figure out some idea of the real ratio.

Answer 3

I may be mistaken but I was under the impression that !=was not good SQL practice, rather <> is preferrable.

e.g.

WHERE Reputation <> 101 AND Reputation <> 1 --Exclude users without repchanges

Answer 4

Incorporing the changes suggested by @Malachi and @JerryCoffin, as well as a little tweaking by myself, the query currently looks as follows:

WITH Voters AS(
   SELECT DISTINCT 
       UpVotes,
       DownVotes,
       Id,
       Reputation,
       --as there is no split between question and answer votes
        -- we take 8 as median in accordance to the tendency that there are more
        -- answer votes (10 rep) than question votes (5 rep)
      (UpVotes * 8) AS EstimatedAddedRep,
       --(-2 Rep) for the downvoted, 1 rep "cost"
      (DownVotes * 3) AS EstimatedDestroyedRep
   FROM Users
   WHERE Reputation != 101 AND Reputation != 1 --Exclude users without repchanges
   )

SELECT TOP ##topX:int?20##
    Users.Id as [User Link],
    Users.DisplayName as [SortName],
    Voters.EstimatedAddedRep, 
    Voters.EstimatedDestroyedRep,
    Users.Reputation as [OwnedRep],
    round((Voters.EstimatedAddedRep * 1.0 / Users.Reputation), 2) as [CreatedToOwnedRatio]
FROM Users INNER JOIN Voters ON Users.Id = Voters.Id
GROUP BY Users.Id, Users.DisplayName,
    Users.Reputation, Voters.EstimatedAddedRep, Voters.EstimatedDestroyedRep
ORDER BY EstimatedAddedRep DESC

I have moved the calculations for EstimatedAddedRep and EstimatedDestroyedRep to the Voters to remove clutter in the actual SELECT-Statement.

Additionally I renamed the Ratio column to better reflect what it means.