Skip to main content
replaced http://codereview.stackexchange.com/ with https://codereview.stackexchange.com/
Source Link

Incorporing the changeschanges suggestedsuggested by @Malachi@Malachi and @JerryCoffin@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.

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.

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.

Source Link
Vogel612
  • 25.3k
  • 7
  • 58
  • 141

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.