1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

DECLARE @output TABLE (
  UserName VARCHAR(max),
  UpVoteNo INT,
  DownVoteNo INT,
  Reputation INT,
  AccountId INT
);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange');

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      DisplayName AS UserName,
      UpVotes AS UpVoteNo,
      DownVotes AS DownVoteNo,
      Reputation,
      Users.AccountId
    FROM [' + @dbname + '].dbo.Users
    WHERE UpVotes > 0 OR DownVotes > 0
    ';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH Raw AS (
  SELECT
    AccountId,
    MAX(UserName) AS UserName,
    SUM(UpVoteNo) AS UpVoteNo,
    SUM(DownVoteNo) AS DownVoteNo,
    1.0 * SUM(DownVoteNo) / ( SUM(DownVoteNo) + SUM(UpVoteNo) ) AS Ratio,
    MAX(Reputation) AS MaxRep,
    SUM(Reputation) AS SumRep
  FROM @output
  GROUP BY AccountId
),
Raw2 AS (
  SELECT
    DENSE_RANK() OVER (ORDER BY DownVoteNo DESC) AS DownRank,
    DENSE_RANK() OVER (ORDER BY UpVoteNo DESC) AS UpRank,
    DENSE_RANK() OVER (ORDER BY Ratio DESC) AS RatioRank,
    'http://stackexchange.com/users/'
      + CAST(AccountId AS VARCHAR(30))
      + '?tab=accounts|' + UserName AS 'SE Account',
    AccountId,
    UpVoteNo,
    DownVoteNo,
    Ratio,
    MaxRep,
    SumRep
  FROM Raw
)
SELECT
  DENSE_RANK() OVER (ORDER BY Uprank + DownRank + 2 * RatioRank) AS Rank,
  *
FROM Raw2
ORDER BY Rank, AccountId;

view current version

edited aug 27 17

created aug 18 17 peterh