Q&A about the site for serious players and enthusiasts of chess
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;