6

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for developers and researchers interested in open data

DECLARE @output TABLE (
  UserName VARCHAR(max),
  VoteNo 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 = '
    WITH RawVotes AS (
      SELECT
        UpVotes + DownVotes AS VoteNo,
        Users.Id AS UserId
      FROM [' + @dbname + '].dbo.Users
      WHERE Reputation >= 15
      
      UNION
      
      SELECT
        COUNT(*) AS VoteNo,
        UserId
      FROM [' + @dbname + '].dbo.Votes
      WHERE UserId IS NOT NULL
      GROUP BY UserId
    )
    SELECT
      MAX(Users.DisplayName) AS UserName,
      SUM(VoteNo) AS VoteNo,
      MIN(Users.Reputation) AS Reputation,
      Users.AccountId
    FROM RawVotes
    LEFT JOIN [' + @dbname + '].dbo.Users ON RawVotes.UserId = Users.Id
    GROUP BY Users.AccountId;
    ';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH CombinedVotes AS (
  SELECT
    MIN(UserName) AS UserName,
    AccountId,
    MAX(Reputation) AS MaxRep,
    SUM(Reputation) AS Reputation,
    SUM(VoteNo) AS VoteNo
  FROM @output
  GROUP BY AccountId
)
SELECT
  DENSE_RANK() OVER (ORDER BY VoteNo DESC) AS Rank,
  'http://stackexchange.com/users/'
    + CAST(AccountId AS VARCHAR(30))
    + '?tab=accounts|' + UserName AS 'SE Account',
  AccountId,
  VoteNo,
  MaxRep,
  Reputation
FROM CombinedVotes
ORDER BY VoteNo DESC, AccountId;

fork query

edited aug 15 17

created aug 15 17 peterh